Databases Reference
In-Depth Information
a few of the columns of the table). Run the following query, which will show the plan in
Figure 2-4.
SELECT
AddressID
,
City
,
StateProvinceID
FROM
Person
.
Address
Listing 2-4.
Figure 2-4:
An Index Scan operator.
Note that the Query Optimizer was able to solve this query without even accessing
the base table
Person.Address
, and instead decided to scan the
IX_Address_
AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
index, which
comprises fewer pages. The index definition includes
AddressLine1
,
AddressLine2
,
City
,
StateProvinceID
and
PostalCode
, so it can clearly cover columns requested in
the query. However, you may wonder where the index is getting the
AddressID
column
from. When a non-clustered index is created on a table with a clustered index, each
non-clustered index row also includes the table clustering key. This clustering key is used
to find which record from the clustered index is referred to by the non-clustered index
row (a similar approach for non-clustered indexes on a heap will be explained later in this
section). In this case, as I mentioned earlier,
AddressID
is the clustering key of the table
and it is stored in every row of the non-clustered index, which is why the index was able
to cover this column in the previous query.
