Microsoft SQL Server 2012 Internals

Index selection

Index selection is one of the most important aspects of query optimization. The basic idea behind index matching is to take predicates from a WHERE clause, join condition, or other limiting operation in a query and to convert that operation so that it can be performed against an index. Two basic operations can be performed against an index.

• Seek for a single value or a range of values on the index key

• Scan the index forward or backward

For seek, the initial operation starts at the root of a B-tree and navigates down the tree to a desired location in the index based on the index keys. When completed, the query processor can iterate over all rows that match the predicate or until the last value in the range is found. Because leaves ...