Database Reference
In-Depth Information
Nonclustered
Index
Index Key
Range
Clustered
Index
(All Table Data)
Index Key
Range
Index Key
Range
Pages of Key
(INCLUDE data
here)
Pages of Key
(INCLUDE data
here)
Pages of Key
(INCLUDE data
here)
Pages of Key
(INCLUDE data
here)
Clustered Key
Figure 11-10.
Index storage using the
INCLUDE
keyword
Another way to get a covering index is to take advantage of the structures within SQL Server. If the previous query
were modified slightly to retrieve a different set of data instead of a particular
NationallDNumber
and its associated
JobTitle
and
HireDate
, this time the query would retrieve the
NationallDNumber
as an alternate key and the
BusinessEntitylD
, the primary key for the table, over a range of values.
SELECT NationalIDNumber,
BusinessEntityID
FROM HumanResources.Employee AS e
WHERE e.NationalIDNumber BETWEEN '693168613'
AND '7000000000';
The original index on the table doesn't reference the
BusinessEntitylD
column in any way.
CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber]
ON [HumanResources].[Employee]
(
[NationalIDNumber] ASC
)WITH DROP_EXISTING ;
When the query is run against the table, you can see the results shown in Figure
11-11
.
Figure 11-11.
Unexpected covering index
