Chapter 8 -- Indexes

Chapter 8

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. You learned some basic information about indexes in Chapter 3, but to really understand the benefit that indexes can provide and how to determine the best indexes for your environment, you need to take a deeper look into the organization of SQL Server indexes.

In this chapter, I'll show you the physical organization of index pages for both types of SQL Server indexes, clustered and nonclustered. I'll discuss the various options available when you create and re-create indexes, and I'll tell you how, when, and why to rebuild your indexes. I'll tell you about SQL Server 2000's online index defragmentation utility and about a tool for determining whether your indexes need defragmenting.

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 successfully retrieve results for your SELECT statements or to find and modify the specified rows in your data modification statements. Most of the examples you saw in Chapter 6 could be executed just fine without any indexes at all. 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, or to efficiently maintain uniqueness of your key columns during INSERT and UPDATE operations. 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.

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. I'll tell you a lot more about the query optimizer in Chapters 15 and 16, 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.