Database Reference
In-Depth Information
As shown in the execution plan, you have a key lookup. The
SELECT
statement refers to columns
NationalIDNumber
,
JobTitle
, and
HireDate
. The nonclustered index on column
NationalIDNumber
doesn't provide
values for columns
JobTitle
and
HireDate
, so a lookup operation was required to retrieve those columns from the
data storage location. It's a
Key Lookup
because it's retrieving the data through the use of the clustered key stored with
the nonclustered index. If the table were a heap, it would be an
RID
lookup. However, in the real world, it usually won't
be this easy to identify all the columns used by a query. Remember that a lookup operation will be caused if all the
columns referred to in any part of the query (not just the selection list) aren't part of the nonclustered index used.
In the case of a complex query based on views and user-defined functions, it may be too difficult to find all the
columns referred to by the query. As a result, you need a standard mechanism to find the columns returned by the
lookup that are not included in the nonclustered index.
If you look at the properties on the
Key Lookup (Clustered)
operation, you can see the output list for the
operation. This shows you the columns being output by the lookup. To get the list of output columns quickly and
easily and be able to copy them, right-click the operator, which in this case is
Key Lookup (Clustered)
. Then select
the Properties menu item. Scroll down to the Output List property in the Properties window that opens (Figure
11-6
).
This property has an expansion arrow, which allows you to expand the column list, and has further expansion arrows
next to each column, which allow you to expand the properties of the column.
Figure 11-6.
Key lookup Properties window
To get the list of columns directly from the Properties window, click the ellipsis on the right side of the Output List
property. This opens the output list in a text window from which you can copy the data for use when modifying your
index (Figure
11-7
).
Figure 11-7.
The required columns that were not available in the nonclustered index
