Tables and Indexes: Internal Structure and Access Methods - Pro SQL Server Internals - page 38

■ We will talk about page splits and fragmentation in Chapter 5, “Index Fragmentation,” and discuss locking and
data consistency in part 3, “locking, blocking, and Concurrency.”

Note

The last index access method is called index seek . Let's assume that you need to run the query shown in
Listing 2-8. Figure 2-12 illustrates the operation.

Listing 2-8. Index seek query

select Name
from dbo.Customers
where CustomerId between 4 and 7

Figure 2-12. Index seek

In order to read the range of rows from the table, SQL Server needs to find the row with the minimum value of
the key from the range, which is 4. SQL Server starts with the root page where the second row references the page
with the minimum key value of 350. It is greater than the key value that we are looking for, and SQL Server reads the
intermediate level data page (1:170) referenced by the first row on the root page.
Similarly, the intermediate page leads SQL Server to the first leaf-level page (1:176). SQL Server reads that page,
then it reads the rows with CustomerId equal 4 and 5, and finally, it reads the two remaining rows from the second page.
The execution plan is shown in Figure 2-13 .