Database Reference
In-Depth Information
Benefits of a Nonclustered Index over a Clustered Index
As you learned in the previous section, a nonclustered index is preferred over a clustered index in the following
situations:
•
When the index key size is large.
•
To avoid the overhead cost associated with a clustered index since rebuilding the clustered
index rebuilds all the nonclustered indexes of the table.
•
To resolve blocking by having a database reader work on the pages of a nonclustered index,
while a database writer modifies other columns (not included in the nonclustered index) in
the data page; in this case, the writer working on the data page won't block a reader that can
get all the required column values from the nonclustered index without hitting the base table.
I'll explain this in detail in Chapter 13.
•
When all the columns (from a table) referred to by a query can be safely accommodated in the
nonclustered index itself, as explained in this section.
As already established, the data-retrieval performance when using a nonclustered index is generally poorer than
that when using a clustered index because of the cost associated with jumping from the nonclustered index rows to the
data rows in the base table. In cases where the jump to the data rows is not required, the performance of a nonclustered
index should be just as good as—or even better than—a clustered index. This is possible if the nonclustered index, the
key plus any included columns at the page level, includes all the columns required from the table.
To understand the situation in which a nonclustered index can outperform a clustered index, consider the following
example. Assume for these purposes that you need to examine the credit cards that are expiring between the months of
June 2008 and September 2008. You may have a query that returns a large number of rows and looks like this:
SELECT cc.CreditCardID,
cc.CardNumber,
cc.ExpMonth,
cc.ExpYear
FROM Sales.CreditCard cc
WHERE cc.ExpMonth BETWEEN 6 AND 9
AND cc.ExpYear = 2008
ORDER BY cc.ExpMonth;
The following are the I/O and time results. Figure
8-24
shows the execution plan.
Table 'CreditCard'. Scan count 1, logical reads 189
CPU time = 16 ms, elapsed time = 240 ms.
Figure 8-24.
Execution plan scanning the clustered index
