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.
|Comparison Operator||Percentage of Rows Estimated|
Nested loop works well with a smaller outer table and an index on the inner table.
Merge works well when both inputs are sorted on the joining column. (The optimizer can sort one ...