Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 253

A simple example will help illustrate the concepts of a hash join. Using the Production.Product-
Category and Production.ProductSubcategory tables in the AdventureWorks database, consider the
following query:

SELECT pc.Name as Category,
ps.Name as Subcategory
FROM Production.ProductCategory pc
INNER JOIN Production.ProductSubcategory ps
ON pc.ProductCategoryID = ps.ProductCategoryID
OPTION (HASH JOIN)

Since Production.ProductCategory is the smaller input, it will be used for the build phase. Thus, a hash
table as shown in Table 9-7 may be generated.

Table 9-7: Hash Table

0 → 2 Components 4 Accessories

1 → 1 Bikes 3 Clothing

Now that the build phase is complete, the probe phase can begin. SQL Server will compute the hash
values on the probe input's equijoin columns using the same hash function that was used in the build
phase. SQL Server will then check the appropriate hash bucket to see if there are any matches. If it finds
a match, then that will be output.

The pseudo code for the hash operation applied to our example is:

for each row in the Production.ProductCategory table (build input)
begin
calculate hash value on
Production.ProductCategory.ProductCategoryID
insert ProductCategory.Name and ProductCategory.ProductCategoryID
into the appropriate hash bucket

end

for each row in the Production.ProductSubcategory table (probe input)
begin
calculate hash value on
Production.ProductSubcategory.ProductCategoryID
for each row in the corresponding hash bucket
if node in the hash bucket joins with the probe input row on
the key columns then output the contents of the hash node and
the probe columns

end

As you've probably determined by now, hash joins are the most intensive of the three join types. There
are several issues concerning hash joins. First, the build process is a blocking process. The build phase
has to completely finish before any rows can be returned. Second is the hash function. SQL Server uses
hash functions that are considerably more complex that the example here. Coming up with a good hash