October 20, 2011 at 3:40 pm
The rule of thumb and suggested best practice is to create indexes with highly selective columns.
I have a table with 40 million rows that I query against frequently for reporting. In addition to a timestamp field, I have bit fields and fields with only 3-5 possible values in this table that are included in probably 75% of my queries.
In indexing this table, my tests have shown that indexes on these fields with low selectivity first are generating seeks that perform much faster than other indexes.
So an index that looks like this: (IntWith4PossValues, IntWith3PossValues, Bit, TimeStamp) performs well and in fact was the index suggested in the execution plan
This seems to go against the rule above, and I am wondering if anyone has further thoughts or insights into this.
October 20, 2011 at 4:40 pm
That 'best practice' is at best vague. There's far more in consideration than just selectivity.
Selectivity is irrelevant when the index is covering. It's very important when the index is not covering as SQL won't use a non-covering index that retrieves more than a very small portion of the table.
My blog's down, but this should help: http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply