Microsoft® SQL Server 2012 Unleashed

Group by Optimization

One way SQL Server can process GROUP BY results is to retrieve the matching detailed data rows into a worktable and then sort the rows and calculate the aggregates on the groups formed. In SQL Server 2012, the Query Optimizer also may choose to use hashing to organize the data into groups and then compute the aggregates.

The hash aggregation strategy uses the same basic method for grouping and calculating aggregates as for a hash join. At the point where the probe input row is checked to determine whether it already exists in the hash bucket, the aggregate is computed if a hash match is found. The following pseudocode summarizes the hash aggregation strategy:

Click here to view code image

create a hash tablefor each row ...