Hints - Inside the SQL Server Query Optimizer - page 217

SELECT SalesOrderID , COUNT (*)
FROM Sales . SalesOrderDetail
GROUP BY SalesOrderID
Listing 7-9.
Figure 7-6: Execution plan using a Stream Aggregate.
Since the SalesOrderDetail table has a clustered index on the SalesOrderID
column, and so the data is already sorted on the GROUP BY column, using a Stream
Aggregate operator is the obvious choice. However, the following query will force a Hash
Aggregate operator, and will produce the plan shown in Figure 7-7, which will, of course,
make the query more expensive than necessary.
SELECT SalesOrderID , COUNT (*)
FROM Sales . SalesOrderDetail
GROUP BY SalesOrderID
OPTION ( HASH GROUP )
Listing 7-10.
Figure 7-7: Execution plan with a HASH GROUP hint.