Guru's Guide to SQL Server Architecture and Internals, The

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 Operator	Percentage of Rows Estimated
=	10
>	30
<	30
BETWEEN	10

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 ...