Database Reference
In-Depth Information
Figure 2-18.
Nonclustered index usage: Step 1
The second row of the intermediate page indicates that the minimum key value on the page is
Boris
, although the
index had not been defined as unique and SQL Server does not know if there are other
Boris
rows stored on the first page.
As a result, it goes to the first leaf page of the index and finds the row with the key value
Boris
and
row-id
equal 7 there.
In our case, the nonclustered index does not have any data besides
CustomerId
and
Name
, and SQL Server needs to
traverse the clustered index tree and obtain the data from other columns from there. This operation is called
Key Lookup
.
In the next step shown in Figure
2-19
, SQL Server comes back to the nonclustered index and reads the second page
from the leaf level. It finds another row with the key value
Boris
and
row-id
93712, and it performs key lookup again.
Figure 2-19.
Nonclustered index usage: Step 2
