All 7 Hash Indexes & Hash Join Operations questions
More in Hash Indexes & Hash Join Operations
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? In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join? 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

What is a hash join, and how does SQL Server use it to combine two tables?

Beginner

Answer

A hash join works by building a temporary hash table in memory from the smaller of the two input sets, based on the join column, and then scanning the larger input, checking each row against that hash table to quickly find matches, which works well when joining large, unsorted data sets without a useful index.
SELECT o.OrderId, c.Name
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
OPTION (HASH JOIN);
Real-world example A reporting query joining two very large, unindexed tables uses a hash join, letting SQL Server efficiently match rows using an in memory hash table instead of comparing every row against every other row.

Common follow-ups: When does SQL Server typically choose a hash join over other join types?;What happens if the hash table needed for the join does not fit in available memory?

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