O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required