Join Order and Type Selection

In addition to choosing indexes and indexable expressions, the optimizer also selects a join order and picks a join strategy for operators that require it. The selection of indexes and join strategy go hand in hand—indexes influence the types of join strategies that are viable, and the join strategy influences the types of indexes the optimizer needs to produce an efficient plan.

SQL Server supports three types of joins.

Table 12.3. Row Estimates for Nonindexable Expressions
Comparison OperatorPercentage of Rows Estimated
=10
>30
<30
BETWEEN10
  1. Nested loop works well with a smaller outer table and an index on the inner table.

  2. Merge works well when both inputs are sorted on the joining column. (The optimizer can sort one ...

Get Guru's Guide to SQL Server Architecture and Internals, The now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.