Mrdenny 
46795 pts. | Mar 3 2008 6:26AM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
Why is the query using the index even though the perecentage of unique values is 52%?
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.
Thank you!
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.
Thank you!
ASKED:
Mar 3 2008 5:09 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
Also when using the index, even through most of the values are the same, they are in the correct order to be quickly processed where with the table the values may or may not be in the correct order so the entire table must be scanned no matter what.
Is your query doing an index seek or an index scan?
