Why is the query using the index even though the perecentage of unique

[bodhi108]

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!

 

[mrdenny]

Here's my answer from the ITKE site:

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?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fredericofonseca]

bodhi108,

Not creating a index with values Y/N can be the best thing to do in some tables.

Imagine a table with 50+ million records.

Most processing on the table is done based on the records with a flag of "N" (for not processed").
e.g. records added with flag "N", but all other extracts and update processes will take place only when that flag is "N", until a final process that changes it to "Y".


In such a table, the number of "N" will normally be a very small percentage of the records on the table, hence the access to those records being extremely fast through the Index.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886