Microsoft® SQL Server 2012 Unleashed

Index Utilization

Now that you have an understanding of table and index structures and the overhead required to maintain your data and indexes, you might want to put things into practice to actually come up with an index design for your database, defining the appropriate indexes to support your queries. To effectively determine the appropriate indexes that should be created, you need to determine whether they’ll actually be used by the SQL Server Query Optimizer. If an index is not being used effectively, it’s just wasting space and creating unnecessary overhead during updates.

The main criterion to remember is that SQL Server does not use an index for the more efficient row locator lookup if at least the first column of the index is not included ...