The Execution Engine - Inside the SQL Server Query Optimizer - page 46

Now let's see an example of a query using the GROUP BY clause; the following query
produces the plan in Figure 2-14:
SELECT ProductLine , COUNT (*) FROM Production . Product
GROUP BY ProductLine
Listing 2-17.
Figure 2-14: Stream Aggregate using a Sort operator.
A Stream Aggregate operator always requires its input to be sorted by the GROUP BY
clause predicate so, in this case, the Sort operator shown in the plan will provide the data
sorted by the ProductLine column. After receiving its input sorted, the records for
the same group will be next to each other, so the Stream Aggregate operator can count
the records for each group. Note that, although the first example of this section was also
using a Stream Aggregate, it did not require any sorted input: a query without a GROUP BY
clause considers its entire input a single group.
A Stream Aggregate can also use an index to have its input sorted, as in the following
query, which produces the plan on Figure 2-15:
SELECT SalesOrderID , SUM ( LineTotal )
FROM Sales . SalesOrderDetail
GROUP BY SalesOrderID
Listing 2-18.