Database Reference
In-Depth Information
Nonclustered Indexes
A nonclustered index does not affect the order of the data in the table pages because the leaf pages of a nonclustered
index and the data pages of the table are separate. A pointer (the row locator) is required to navigate from an index
row in the nonclustered index to the data row, whether stored on a cluster or in a heap. As you learned in the earlier
“Clustered Indexes” section, the structure of the row locator depends on whether the data pages are stored in a heap
or a clustered index. For a heap, the row locator is a pointer to the RID for the data row; for a table with a clustered
index, the row locator is the clustered index key.
Nonclustered Index Maintenance
The row locator value of the nonclustered indexes continues to have the same clustered index value, even when the
clustered index rows are physically relocated.
In a table that is a heap, where there is no clustered index, to optimize this maintenance cost, SQL Server adds
a pointer to the old data page to point to the new data page after a page split, instead of updating the row locator of
all the relevant nonclustered indexes. Although this reduces the maintenance cost of the nonclustered indexes, it
increases the navigation cost from the nonclustered index row to the data row within the heap, since an extra link
is added between the old data page and the new data page. Therefore, having a clustered index as the row locator
decreases this overhead associated with the nonclustered index.
Defining the Lookup Operation
When a query requests columns that are not part of the nonclustered index chosen by the optimizer, a lookup is required.
This may be a key lookup when going against a clustered index or an RID lookup when performed against a heap. In the
past, the common term for these lookups came from the old definition name,
bookmark lookup
. That term is being used
less and less since people haven't seen that phrase in execution plans since SQL Server 2000. Now you just refer to it as a
lookup and then define the type, key, or RID. The lookup fetches the corresponding data row from the table by following
the row locator value from the index row, requiring a logical read on the data page besides the logical read on the index
page and a join operation to put the data together in a common output. However, if all the columns required by the query
are available in the index itself, then access to the data page is not required. This is known as a
covering index
.
These lookups are the reason that large result sets are better served with a clustered index. A clustered index
doesn't require a lookup, since the leaf pages and data pages for a clustered index are the same.
■
Note
Chapter 11 covers lookup operations in more detail.
Nonclustered Index Recommendations
Since a table can have only one clustered index, you can use the flexibility of multiple nonclustered indexes to help
improve performance. I explain the factors that decide the use of a nonclustered index in the following sections.
When to Use a Nonclustered Index
A nonclustered index is most useful when all you want to do is retrieve a small number of rows and columns from a
large table. As the number of columns to be retrieved increases, the ability to have a covering index decreases. Then,
if you're also retrieving a large number of rows, the overhead cost of any lookup rises proportionately. To retrieve a
small number of rows from a table, the indexed column should have a high selectivity.
