> is it possible to create an index, but exclude records with values NULL
> and 0 in the index column for the index?
>
> I have a log table with millions of records, but maybe only 5-10% have a
> value in this specific field. And if I ever do a query using this value,
> only records with values <> NULL or 0 are relevant.

I have a similar problem. I have an IsActive field (containing only 2 different values representing Yes/No), and usually only a small percentage of records are still active (and needed in calculations) (so, it is usually a good index to use, even though it has a low selectivity)


I'm thinking of changing all those types of fields over to a date instead, and calling it DateDeactivated. I'll then be able to extract the few records where DateDeactivated is null to get the few remaining active ones.

I haven't made the leap yet (there's an awful lot of tables to change), but I'm hoping it's going to work nicely..