Microsoft® SQL Server 2012 Unleashed

Evaluating Index Usefulness

SQL Server provides indexes for two primary reasons: as a method to enforce the uniqueness of the data in the database tables and to provide faster access to data in the tables. Creating the appropriate indexes for a database is one of the most important aspects of physical database design. Because you can’t have an unlimited number of indexes on a table, and it wouldn’t be feasible anyway, you should create indexes on columns that have high selectivity so that your queries will use the indexes. The selectivity of an index can be defined as follows:

Selectivity ratio = Number of unique index values / Number of rows in table

If the selectivity ratio is high—that is, if a large number of rows can be uniquely identified ...