Microsoft® SQL Server 2012 Unleashed

Index Design Guidelines

SQL Server indexes are mostly transparent to end users and T-SQL developers and are typically not specified in queries unless you use table hints to force the Query Optimizer to use a particular index. (Although forcing indexes is generally not advised, using Query Optimizer table hints is covered in more detail in Chapter 35.) Normally, based on the index key histogram or density values, the SQL Server cost-based Query Optimizer automatically chooses the index that is least expensive from an I/O standpoint.

Chapter 31 goes into greater detail on how the Query Optimizer estimates I/O and determines the most efficient query plan. In the meantime, the following are some of the main guidelines to follow in creating useful ...