Database Reference
In-Depth Information
the query at all. The other columns (
LineTotal
,
CarrierTrackingNumber
,
OrderQty
, and
LineTotal
) referred to by
the query are not available in the nonclustered index. To fetch the values for those columns, navigation from the
nonclustered index row to the corresponding data row through the clustered index is required, and this operation is a
key lookup. You can see this in action in Figure
11-1
.
Figure 11-1.
Key lookup in part of a more complicated execution plan
To better understand how a nonclustered index can cause a lookup, consider the following
SELECT
statement,
which requests only a few rows but all columns because of the wildcard
*
from the
SalesOrderDetail
table by using a
filter criterion on column
ProductID
:
SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 776 ;
The optimizer evaluates the
WHERE
clause and finds that the column
ProductID
included in the
WHERE
clause
has a nonclustered index on it that filters the number of rows down. Since only a few rows, 228, are requested, retrieving
the data through the nonclustered index will be cheaper than scanning the clustered index (containing more than
120,000 rows) to identify the matching rows. The nonclustered index on the column
ProductID
will help identify the
matching rows quickly. The nonclustered index includes the column
ProductID
and the clustered index columns
SalesOrderID
and
SalesOrderDetailID
; all the other columns being requested are not included. Therefore, as you
may have guessed, to retrieve the rest of the columns while using the nonclustered index, you require a lookup.
This is shown in the following metrics and in the execution plan in Figure
11-2
(you can turn on
STATISTICS IO
using the Query
➤
Query Options menu). Look for the
Key Lookup (Clustered)
operator. That is the lookup
in action.
Table 'SalesOrderDetail'. Scan count 1, logical reads 710
CPU time = 0 ms, elapsed time = 104 ms.
Figure 11-2.
Execution plan with a bookmark lookup
