Database Reference
In-Depth Information
Furthermore, there will be indexing requirements that won't be suitable for a clustered index, as explained in the
“Clustered Indexes” section.
•
Frequently updatable columns
•
Wide keys
In these cases, you can use a nonclustered index since, unlike a clustered index, it doesn't affect other indexes in
the table. A nonclustered index on a frequently updatable column isn't as costly as having a clustered index on that
column. The
UPDATE
operation on a nonclustered index is limited to the base table and the nonclustered index. It
doesn't affect any other nonclustered indexes on the table. Similarly, a nonclustered index on a wide column (or set of
columns) doesn't increase the size of any other index, unlike that with a clustered index. However, remain cautious,
even while creating a nonclustered index on a highly updatable column or a wide column (or set of columns), since
this can increase the cost of action queries, as explained earlier in the chapter.
■
a nonclustered index can also help resolve blocking and deadlock issues. I cover this in more depth in
Chapters 20 and 21.
Tip
When Not to Use a Nonclustered Index
Nonclustered indexes are not suitable for queries that retrieve a large number of rows. Such queries are better
served with a clustered index, which doesn't require a separate lookup to retrieve a data row. Since a lookup requires
additional logical reads to get to the data page besides the logical read on the nonclustered index page, the cost of a
query using a nonclustered index increases significantly for a large number of rows, such as when in a loop join that
requires one lookup after another. The SQL Server query optimizer takes this cost into effect and accordingly can
discard the nonclustered index when retrieving a large result set.
If your requirement is to retrieve a large result set from a table, then having a nonclustered index on the filter
criterion (or the join criterion) column will probably not be useful unless you use a special type of nonclustered index
called a
covering index.
I describe this index type in detail in Chapter 9.
Clustered vs. Nonclustered Indexes
The main considerations in choosing between a clustered and a nonclustered index are as follows:
•
Number of rows to be retrieved
•
Data-ordering requirement
•
Index key width
•
Column update frequency
•
Lookup cost
•
Any disk hot spots
Benefits of a Clustered Index over a Nonclustered Index
When deciding upon a type of index on a table with no indexes, the clustered index is usually the preferred choice.
Because the index page and the data pages are the same, the clustered index doesn't have to jump from the index row
to the base row as is required in the case of a noncovering nonclustered index.
