Databases Reference
In-Depth Information
Figure 2-6:
An Index Seek operator.
Bookmark lookup
The question that now comes up is what happens if a non-clustered index is useful
to quickly find one or more records, but does not cover the query? In other words, what
happens if the non-clustered index does not contain all of the columns requested by
the query? In this case, the Query Optimizer has to decide if it is more efficient to both
use the non-clustered index to find these records quickly and also access the base table
to obtain the additional fields, or to just go straight to the base table and scan it. For
example, on the previous query on Listing 2-6, an existing non-clustered index covers
both
AddressID
and
StateProvinceID
columns. What about if we also request the
City
and
ModifiedDate
columns on the same query? This is shown in the next query,
which returns one record and produces the plan in Figure 2-7.
SELECT
AddressID
,
City
,
StateProvinceID
,
ModifiedDate
FROM
Person
.
Address
WHERE
StateProvinceID
=
32
Listing 2-7.
