Database Reference
In-Depth Information
Figure 9-2.
Query with a covering index
The reads have dropped from 19 to 2, and the execution plan is just about as simple as possible; it's a single
Index Seek
operation against the new and improved index, which is now covering. A covering index is a useful
technique for reducing the number of logical reads of a query. Adding columns using the
INCLUDE
statement makes
this functionality easier to achieve without adding to the number of columns in an index or the size of the index key
since the included columns are stored only at the leaf level of the index.
The
INCLUDE
is best used in the following cases:
•
You don't want to increase the size of the index keys, but you still want to make the index a
covering index.
•
You have a data type that cannot be an index key column but can be added to the
nonclustered index through the
INCLUDE
command.
•
You've already exceeded the maximum number of key columns for an index (although this is a
problem best avoided).
A Pseudoclustered Index
The covering index physically organizes the data of all the indexed columns in a sequential order. Thus, from a
disk I/O perspective, a covering index that doesn't use included columns becomes a clustered index for all queries
satisfied completely by the columns in the covering index. If the result set of a query requires a sorted output, then the
covering index can be used to physically maintain the column data in the same order as required by the result set—it
can then be used in the same way as a clustered index for sorted output. As shown in the previous example, covering
indexes can give better performance than clustered indexes for queries requesting a range of rows and/or sorted
output. The included columns are not part of the key and therefore wouldn't offer the same benefits for ordering as
the key columns of the index.
Recommendations
To take advantage of covering indexes, be careful with the column list in
SELECT
statements to move only the data
you need to. It's also a good idea to use as few columns as possible to keep the index key size small for the covering
indexes. Add columns using the
INCLUDE
statement in places where it makes sense. Since a covering index includes
all the columns used in a query, it has a tendency to be very wide, increasing the maintenance cost of the covering
indexes. You must balance the maintenance cost with the performance gain that the covering index brings. If the
number of bytes from all the columns in the index is small compared to the number of bytes in a single data row of
that table and you are certain the query taking advantage of the covered index will be executed frequently, then it may
be beneficial to use a covering index.
■
Tip
Covering indexes can also help resolve blocking and deadlocks, as you will see in Chapters 19 and 20.
