Index Selection - Inside the SQL Server Query Optimizer - page 111

The Mechanics of Index Selection
In a seek operation, SQL Server navigates throughout the B-tree index to quickly find the
required records without the need for an index or table scan. This is similar to using an
index at the end of a topic to ind a topic quickly, instead of reading the entire topic. Once
the first record has been found, SQL Server can then scan the index leaf level forward or
backward to find additional records. Both equality and inequality operators can be used
in a predicate, including = , < , > , <= , >= , <> , != , !< , !> , BETWEEN , and IN . For example, the
following predicates can be matched to an Index Seek operation if there is an index on
the specified column, or a multi-column index with that column as a leading index key:
• ProductID = 771
• UnitPrice < 3.975
• LastName = 'Allen'
• LastName LIKE 'Brown%'
As an example, look at the next query, which uses an Index Seek operator and produces
the plan in Figure 4-1.
SELECT ProductID , SalesOrderID , SalesOrderDetailID
FROM Sales . SalesOrderDetail
WHERE ProductID = 771
Listing 4-1.
Figure 4-1: Plan with Index Seek.