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

20 pts.
Tags:
Indexes
SQL
SQL performance
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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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?

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following