>For example if a persons table contains about 1 or 2 % of discontinued products, I certainly don't want an index on the field 'discontinued' because the selectivity of the index is very low.
>
>When I make a selection of all available products, I don't want rushmore to filter the discontinued products out (note that this Discontinued thing can be compared with the DELETED() tag, which is in fact just another field), because the optimizable command might run faster without it.
Do you set deleted on?
I lean towards the other direction with respect to indexing. When in doubt, index it. When users start to add off the wall reports later, the criterior that they choose is rarely what I considered in the front end design of the app. It's just a CYA kind of thing.
Will I determine if the users will delete records or not? I don't think that I can make that call. My users are more like Steve Urkle, "Did I do that?"