Database Reference
In-Depth Information
Figure 11-8.
Execution plan with a covering index
There are a couple of caveats to creating a covering index by changing the key, however. If you add too many
columns to a nonclustered index, it becomes wider. The index maintenance cost associated with the action queries
can increase, as discussed in Chapter 8. Therefore, evaluate closely whether adding a key value will provide benefits
to the general use of the index. If a key value is not going to be used for searches within the index, then it doesn't
make sense to add it to the key. Also evaluate the number of columns (for size and data type) to be added to the
nonclustered index key. If the total width of the additional columns is not too large (best determined through testing
and measuring the resultant index size), then those columns can be added in the nonclustered index key to be used
as a covering index. Also, if you add columns to the index key, depending on the index, of course, you may be affecting
other queries in a negative fashion. They may have expected to see the index key columns in a particular order or may
not refer to some of the columns in the key, causing the index to not be used by the optimizer. Only modify the index
by adding keys if it makes sense based on these evaluations, especially because you have an alternative to modifying
the key.
Another way to arrive at the covering index, without reshaping the index by adding key columns, is to use the
INCLUDE
columns. Change the index to look like this:
CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber]
ON [HumanResources].[Employee]
(NationalIDNumber ASC)
INCLUDE (JobTitle,HireDate)
WITH DROP_EXISTING ;
Now when the query is run, you get the following metrics and execution plan (Figure
11-9
):
Table 'Employee'. Scan count 1, logical reads 2
CPU time = 0 ms, elapsed time = 0 ms.
Figure 11-9.
Execution plan with INCLUDE columns
The index is still covering, exactly as it was in the execution plan displayed in Figure
11-8
. Because the data is
stored at the leaf level of the index, when the index is used to retrieve the key values, the rest of the columns in the
INCLUDE
statement are available for use, almost like they were part of the key. Refer to Figure
11-10
.
