Aggregations

Just like join algorithms, aggregation algorithms can also be forced by using the GROUP hints. Specifically, the ORDER GROUP hint requests that the query optimizer use a Stream Aggregate algorithm, while the HASH GROUP hint requests a Hash Aggregate algorithm. These hints can only be specified at the query level, so they will impact all the aggregation operations in the query. To see the effects of this, take a look at the following unhinted query, which uses a Stream Aggregate operator:

SELECT SalesOrderID, COUNT(*)

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

This produces the following plan:

Figure 12.8 – A query using Stream Aggregate

Because the SalesOrderDetail table has a clustered index on the SalesOrderID column, and therefore the data is already sorted on the GROUP BY column, using a Stream Aggregate operator is the obvious choice. However, if we add a HASH GROUP hint to the previous query, as shown here,...