Introduction

A lot of factors affect SQL Server and the applications that use its data resources. Gaining performance and response time from a server is a primary concern for database developers and administrators. One key element in obtaining the utmost from a database is an indexing strategy that helps to achieve the business needs of the enterprise, in part by returning data from queries in a responsive fashion.

The query optimizer in SQL Server reliably chooses the most effective index for use with any given query, which helps considerably if indexes have been created to choose from. In the majority of cases the optimizer provides the best performance from the index choices provided. The overall index design strategy should provide a good selection of indexes for the optimizer to use.

You can't just start putting a lot of indexes throughout your table structures until you understand the implications of indexes. Though indexes provide good query performance, they can also take away from other processes. Indexes consume hardware resources and processing cycles; memory overhead is associated with their use; regular maintenance is required to keep them at optimum levels; and many database processes have to work within the presence of the index.

This chapter covers SQL Server indexing strategies from the most basic starting points to advanced tuning and design considerations. The exam covers indexing in many categories, including index selection, tuning, maintenance, and specialty implementations .