Databases Reference
In-Depth Information
Seeking
Now let us look at Index Seeks, which can be performed by both the Clustered Index
Seek and the Index Seek operators and which are used against clustered and non-
clustered indexes, respectively. An Index Seek does not scan the entire index, but instead
navigates the B-tree index structure to quickly find one or more records. The next query,
together with the plan on Figure 2-5, shows an example of a Clustered Index Seek. A
benefit of a Clustered Index Seek, compared to a non-clustered Index Seek, is that the
former can cover any column of the table. Of course, since the records of a clustered
index are logically ordered by its clustering key, a table can only have one clustered index.
SELECT
AddressID
,
City
,
StateProvinceID
FROM
Person
.
Address
WHERE
AddressID
=
12037
Listing 2-5.
Figure 2-5:
A Clustered Index Seek operator.
The next query and Figure 2-6 both illustrate a non-clustered Index Seek operator. It is
interesting to note here that the base table was not used at all and it was not even neces-
sary to scan the entire index: there is a non-clustered index on the
StateProvinceID
and, as mentioned previously, it also contains the clustering key
AddressID
.
SELECT
AddressID
,
StateProvinceID
FROM
Person
.
Address
WHERE
StateProvinceID
=
32
Listing 2-6.
