Databases Reference
In-Depth Information
The output will show the following text plan including a Clustered Index Seek operator
with the
LOOKUP
keyword at the end:
|--Nested Loops(Inner Join, OUTER REFERENCES …)
|--Index Seek(OBJECT:([Address].[IX_Address_StateProvinceID]),
SEEK:([Address].[StateProvinceID]=(32)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Address].[PK_Address_AddressID]),
SEEK:([Address].[AddressID]=[Address].[AddressID]) LOOKUP ORDERED FORWARD)
Listing 2-9.
The XML plan shows the same information in the following way:
<
RelOp
…
PhysicalOp
=
"
Clustered Index Seek
"
…>
…
<
IndexScan
Lookup
=
"
true
"
…
>
Listing 2-10.
Keep in mind that, although SQL Server 2000 implemented a bookmark lookup using a
dedicated operator (called Bookmark Lookup), the operation is basically the same.
Now run the same query but, this time, request
StateProvinceID
equal to 20. This will
produce the plan shown in Figure 2-8.
SELECT
AddressID
,
City
,
StateProvinceID
,
ModifiedDate
FROM
Person
.
Address
WHERE
StateProvinceID
=
20
Listing 2-11.
