A join hint directs SQL Server toward a specified physical join algorithm for a logical join. Use one only as a last resort: stale statistics, indexes, or other changes may offer safer fixes. section 10.5
Mistake: freezing today’s best join
A poorly performing join between Marketing and MarketingArchive uses Nested Loops. Forcing Hash Join with INNER HASH JOIN reduces the example runtime to 5 seconds, but later indexes on ImpressionUID make Merge Join faster at 3 seconds. The forced Hash Join takes 4 seconds instead. Prefer a query hint that permits MERGE JOIN or HASH JOIN when both remain viable. 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 Nested Loops, Hash Join, and Merge Join by operating pattern and situations where each is most efficient. section 10.5
