Databases Reference
In-Depth Information
Hash Join
The third join algorithm used by SQL Server is the Hash Join. Run the following query to
produce the plan displayed in Figure 2-30, and then we'll take a closer look at the Hash
Join operator.
SELECT
pv
.
ProductID
,
v
.
VendorID
,
v
.
Name
FROM
Purchasing
.
ProductVendor pv
JOIN
Purchasing
.
Vendor v
ON
(
pv
.
VendorID
=
v
.
VendorID
)
WHERE
StandardPrice
>
$10
Listing 2-32
Figure 2-30:
A Hash Join example.
In the same way as the Merge Join, the Hash Join requires an equality operator on the
join predicate but, unlike the Merge Join, it does not require its inputs to be sorted. In
addition, its operations in both inputs are executed only once, which you can verify by
looking at the operator properties as shown before. However, a Hash Join works by
creating a hash table in memory. The Query Optimizer will use a cardinality estimation
to detect the smaller of the two inputs, called the build input, and will use it to build a
hash table in memory. If there is not enough memory to host the hash table, SQL Server
can use disk space, creating a workfile in
tempdb
. A Hash Join will also block, but only
during the time the build input is hashed. After the build input is hashed, the second
