Question

  Asked: Mar 3 2008   5:09 AM GMT
  Asked by: Abbi


Why is the query using the index even though the perecentage of unique values is 52%?


Indexes, SQL performance, SQL

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!

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



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?
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database and Development.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Mar 3 2008  6:26AM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.