Chapter 7. Index Internals and Management

In this chapter:
Index Organization	250
Creating an Index	254
The Structure of Index Pages	259
Index Space Requirements	275
Special Indexes	280
Table and Index Partitioning	288
Data Modification Internals	296
Managing Indexes	314
Using Indexes	329
Summary	330

Indexes are the other significant user-defined, on-disk data structure besides tables. An index provides fast access to data when the data can be searched by the value that is the index key. I included some very basic information about indexes in Chapter 2, but to really understand the benefit that indexes can provide and how to determine the best indexes for your environment, we need to take a deeper look into the organization of Microsoft SQL Server indexes.

In this chapter, I'll show you the physical organization of index pages for the two types of SQL Server relational indexes, clustered and nonclustered. I'll discuss the options available when you create and re-create relational indexes, and I'll tell you how, when, and why to rebuild your indexes. I'll tell you about the SQL Server 2005 online index rebuilding capability and about the metadata you can use to determine whether your indexes need defragmenting. I will not discuss XML indexes, which are non-relational; they are outside the scope of this book.

This chapter will also cover the internals of data modification operations. Although data modifications can obviously be performed on heaps as well as on indexes, a complete understanding of SQL Server processing of data modifications depends on an understanding of index structures. Finally, I will discuss the ability of SQL Server 2005 to separate data into partitions. Like data modification, this topic can also be relevant to heaps, but because I wanted to discuss table and index partitioning at the same time, I could not address that topic before this point.

Indexes allow data to be organized in a way that allows optimum performance when you access or modify it. SQL Server does not need indexes to retrieve results for your SELECT statements successfully or to find and modify the specified rows in your data modification statements. However, as your tables get larger, the value of using proper indexes becomes obvious. You can use indexes to quickly find data rows that satisfy conditions in your WHERE clauses, to find matching rows in your JOIN clauses, to effectively enforce referential integrity, or to efficiently maintain uniqueness of your key columns during INSERT and UPDATE operations. Indexes are also useful for achieving vertical partitioning when subsets of columns are required in many queries. In some cases, you can use indexes to help SQL Server sort, aggregate, or group your data or to find the first few rows as indicated in a TOP clause (if the query also includes an ORDER BY clause).

It is the job of the query optimizer to determine which indexes, if any, are most useful in processing a specific query. The final choice of which indexes to use is one of the most important components of the query optimizer's execution plan. The basics of the optimizer's index selection criteria are described in Chapter 3 of Inside Microsoft SQL Server 2005: T-SQL Querying. I'll discuss many more details about the optimizer in Inside Microsoft SQL Server 2005: Query Tuning and Optimization, including the factors that determine whether the query optimizer actually chooses to use indexes at all. In this chapter, I'll focus on what indexes look like and how they can speed up your queries.