I have a table with no primary key and a non-clustered composite index of two columns. When I look at the number of distinct values (selectivity ratio), the table has a ratio of 52% unique values. I thought an index should be at least 90% unique in order for it to be used. Yet when I run the query with the index, it only takes 2 seconds to run. When I drop the index, it takes 1.25 minutes to run. Why is it using the index when the index is only 52% unique?
One of the examples always given is to never have an index on a column that is a flag because it only has 50% distinct values (YES, NO). So, what am I misunderstanding here.
March 3, 2008 5:09 AM
March 3, 2008 1:20 PM