Databases Reference
In-Depth Information
Figure 2-7:
A bookmark lookup example.
As in the previous example, the Query Optimizer is choosing the index
IX_Address_
StateProvinceID
to find the records quickly. However, because the index does not
cover the additional columns, it also needs to use the base table (in this case the clustered
index) to get that additional information. This operation is called a
bookmark lookup
,
and it is performed by the Key Lookup operator, which was introduced specifically to
differentiate a bookmark lookup from a regular Clustered Index Seek. Actually, the Key
Lookup operator only appears on a graphical plan (and then only from SQL Server
2005 Service Pack 2 and onwards), although text and XML plans can also show if a
Clustered Index Seek operator is performing a bookmark lookup. For example, run
the following query:
SET
SHOWPLAN_TEXT
ON
GO
SELECT
AddressID
,
City
,
StateProvinceID
,
ModifiedDate
FROM
Person
.
Address
WHERE
StateProvinceID
=
32
GO
SET
SHOWPLAN_TEXT
OFF
GO
Listing 2-8.
