All 7 Hash Indexes & Hash Join Operations questions
More in Hash Indexes & Hash Join Operations
What is a hash join, and how does SQL Server use it to combine two tables? What is a hash index, and how is it different from the more common B-tree index used for regular disk based tables? How does the BUCKET_COUNT setting affect the performance of a hash index on a memory optimized table? What are the differences in how a hash index and a memory optimized nonclustered index handle range queries and equality lookups? What happens when a hash join does not have enough memory available to build its hash table entirely in memory, and how does this affect performance? How would you troubleshoot a query that is unexpectedly using a slow hash join when a much faster index seek and nested loop join should be possible?
Advertisement
SQL Server › Hash Indexes & Hash Join Operations

In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join?

Intermediate

Answer

The optimizer tends to choose a hash join when joining two relatively large tables that lack a useful index on the join column, or when at least one input is not already sorted, since building and probing a hash table can be more efficient in these cases than the alternatives, which perform better with smaller inputs, existing indexes, or already sorted data.
-- Large, unindexed tables often lead to a hash join
SELECT * FROM LargeTable1 t1
JOIN LargeTable2 t2 ON t1.Id = t2.Id;
Real-world example A database administrator reviewing an execution plan sees a hash join being used to combine two large staging tables that have no indexes yet, recognizing this as an expected and often reasonable choice for that specific scenario.

Common follow-ups: Does adding an index change which join type the optimizer chooses?;Can you see the estimated memory grant a hash join will require before it runs?

Query Optimization & Plans;Indexes
All 7 Hash Indexes & Hash Join Operations questions