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!
Software/Hardware used:
Software/Hardware used:
ASKED:
March 3, 2008 5:09 AM
UPDATED:
March 3, 2008 1:20 PM
Answer Wiki:
SQL will always try to use an index as even if the index has a low unique value ratio as loading the index off of the disk requires less reads from the disk than loading the entire table from disk as the table will take more data pages then just the index.
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?
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.