Types of Indexes

SQL Server uses two types of indexes: clustered and nonclustered. The primary difference between the two index types is the way the data is stored in the tables?randomly for nonclustered indexes and sorted for clustered indexes. The following sections describe each type of index and suggest where one or the other might be appropriate.

Clustered Indexes

In a clustered index, the table data is actually stored sorted on the key value(s) in the index. Some would say that all table data is stored sorted in the index; others would say that the leaf, or lowest, level of the index is actually the table. Either way you look at it, the concept of a heap no longer applies. As the table data can be physically sorted only one way, only one clustered index can be created per table. Clustered indexes give fast access to values frequently searched by range, or that are accessed in sorted order. For example, for a table storing phone book data, an index on LastName, FirstName would be a better candidate for a clustered index than the PhoneNumber column. A search for Smith, John would be able to efficiently retrieve all the matching records, as they are stored "clustered" together.

Nonclustered Indexes

Nonclustered indexes, as the name implies, do not reorder the table data. They don't require the table to be reordered, so multiple nonclustered indexes can be created per table. You can create up to 249 nonclustered indexes per table, although it is doubtful you will ever need that many. Nonclustered indexes can be created on tables with clustered indexes, and in this case, use the clustered index key to locate rows in the table. As the table data is not stored sorted on the nonclustered key, range scans are inefficient, but equality searches are fast.

To continue with the phone book example, if you knew the phone number, but needed the matching name or address, creating a nonclustered index on PhoneNumber would speed the search, as this would be an equality search. Queries for a name and address to match a phone number, perhaps in response to a 911 call, would be able to use the nonclustered index to speed the response. Also, with the combination of the two indexes in place, queries to a telephone operator for the phone number of Smith, John could be efficiently handled by the clustered index, returning the last name and first name, and the nonclustered index returning the phone number. This is referred to as a covered query, as the indexes were able to return or "cover" all requested fields.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features