A merge join scans two inputs in matching key order, comparing their keys to find joined rows. It can be faster when suitable indexes provide that order, but forcing another join strategy can leave the optimizer unable to benefit from later index changes. section 10.5
Mistake: forcing the plan that worked once
A query joining Marketing.marketing.Impressions to MarketingArchive.dbo.ImpressionsArchive took five seconds with a hash-join hint. After an ImpressionUID index was added, the optimizer could use a merge join in three seconds, but the old hint still forced a four-second hash join. Prefer giving the optimizer multiple viable choices when a hint is necessary. section 10.5
Table 10.1 Physical join operators (view table figure)
| Operator |
Description |
Most efficient use |
|---|---|---|
| Nested Loops |
Choose one table as the outer input table and the other table becomes the inner input table. For each key in the outer input table, it searches every row in the inner input table for matching key values. |
Small tables |
| Hash Join |
Builds a hash table in memory, with each row of the first table placed in a hash bucket, dependent on the key value. The keys of the second table are then hashed and compared to the first table on a row-by-row basis. If there is not enough memory for every row in the first table, then the operation will be performed in multiple steps. |
Large tables not sorted by join key or where inputs have a big difference in row count |
| Merge Join |
Checks the equality of the first key in both tables. It then compares the next row in the second table to the first row in the first table. This is similar to nested loops, but because the inputs are sorted by the join key, it can stop as soon as it finds a row in the second table that is not matched and move to the next row in the first table. |
Large tables sorted by join key, where each table is a similar size |
| Adaptive Join |
The query starts by using a hash join but can change to nested loops during execution if the table that populates the hash table is small enough for nested loops to be more efficient. |
Queries where the input rows wildly swing and Batch Mode execution is used |
Compare merge join with nested loops and hash join by input ordering, table size, and join-key suitability. section 10.5
