Database Reference
In-Depth Information
Figure 11-15.
A Key Lookup operation
The lookup is caused since all the columns referred to by the
SELECT
statement and
WHERE
clause are not included
in the nonclustered index on column
VendorID
. Using the nonclustered index is still better than not using it, since that
would require a scan on the table (in this case, a clustered index scan) with a larger number of logical reads.
To avoid the lookup, you can consider a covering index on the column
OrderDate
, as explained in the previous
section. But in addition to the covering index solution, you can consider an index join. As you learned, an index join
requires narrower indexes than the covering index and thereby provides the following two benefits.
•
Multiple narrow indexes can serve a larger number of queries than the wide covering index.
•
To avoid the lookup using an index join, create a narrow nonclustered index on column
OrderDate
that is not
included in the existing nonclustered index.
Narrow indexes require less maintenance overhead than the wide covering index.
CREATE NONCLUSTERED INDEX Ix_TEST
ON Purchasing.PurchaseOrderHeader(OrderDate);
If you run the
SELECT
statement again, the following output and the execution plan shown in Figure
11-16
are returned:
Table 'PurchaseOrderHeader'. Scan count 2, logical reads 4
CPU time = 0 ms, elapsed time = 28 ms.
Figure 11-16.
Execution plan without a lookup
