Professional SQL Server™ 2005 Programming

Chapter 8. SQL Server — Storage and Index Structures

Indexes are a critical part of your database planning and system maintenance. They provide SQL Server (and any other database system for that matter) with additional ways to look up data and take shortcuts to that data's physical location. Adding the right index can cut huge percentages of time off your query executions. Unfortunately, too many poorly planned indexes can actually increase the time it takes for your query to run. Indeed, indexes tend to be one of the most misunderstood objects that SQL Server offers and, therefore, also tend to be one of the most mismanaged.

We will be studying indexes rather closely in this chapter from both a developer's and an administrator's point of view, but in order to understand indexes, we also need to understand how data is stored in SQL Server. For that reason, we will also take a look at SQL Server's data storage mechanism including the index allocation strategies SQL Server employs and internal structures.