Using Indexes

This chapter has primarily been concerned with the structure of indexes and the nuances of creating and managing them. Hopefully, you're aware of at least some of the places where indexes can be useful in your SQL Server applications. I will list a few of the situations in which you can benefit greatly from indexes; the details of how SQL Server decides which indexes to use, and how you can decide which indexes are the best to build for your queries, are explained in Inside Microsoft SQL Server 2005: Tuning and Optimization.

Looking for Rows

The most straightforward use of an index is to help SQL Server find one or more rows in a table that satisfy a certain condition. For example, if you're looking for all the customers who live in a particular state (for example, your WHERE clause is WHERE state = 'WI'), you can use an index on the state column to find those rows more quickly. SQL Server can traverse the index from the root, comparing index key values to 'WI' to determine whether 'WI' exists in the table and then find the data associated with that value.

Joining

A typical join tries to find all the rows in one table that match rows in another table. Of course, you can have joins that aren't looking for exact matching, but you're looking for some sort of relationship between tables, and equality is by far the most common relationship. A query plan for a join frequently starts with one of the tables, finds the rows that match the search conditions, and then uses the join key in the qualifying rows to find matches in the other table. An index on the join column in the second table can be used to quickly find the rows that match.

Sorting

A clustered index stores the data logically in sorted order. The data pages are linked together in order of the clustering keys. If you have a query to ORDER BY the clustered keys or by the first column of a composite clustered key, SQL Server does not have to perform a sort operation to return the data in sorted orderit can just follow the page linkage in the data pages. There are also cases where SQL Server can use the ordering that exists in the leaf level of nonclustered index to avoid having to actually perform a sort operation.

Grouping

One way that SQL Server can perform a GROUP BY operation is by first sorting the data by the grouping column. For example, if you want to find out how many customers live in each state, you can write a query with a GROUP BY state clause. A clustered index on state will have all the rows with the same value for state in logical sequence, so the grouping operations can be done very quickly.

Maintaining Uniqueness

Creating a unique index (or defining a PRIMARY KEY or UNIQUE constraint that builds a unique index) is by far the most efficient method of guaranteeing that no duplicate values are entered into a column. By traversing an index tree to determine where a new row should go, SQL Server can detect within a few page reads that a row already has that value. Unlike all the other uses of indexes described in this section, using unique indexes to maintain uniqueness isn't just one option among others. Although SQL Server might not always traverse a particular unique index to determine where to try to insert a new row, it will always use the existence of a unique index to verify whether a new set of data is acceptable.