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

CREATE INDEX IX_ContactID ON Sales . SalesOrderHeader ( ContactID )
Listing 2-20.
Figure 2-18: A Stream Aggregate using an index.
To clean up, drop the index using the following DROP INDEX statement:
DROP INDEX Sales . SalesOrderHeader . IX_ContactID
Listing 2-21.
If the input is not sorted and order is explicitly requested in a query, the Query Optimizer
may introduce a Sort operator and a Stream Aggregate as shown previously, or it may
decide to use a Hash Aggregate and then sort the results as in the following query, which
produces the plan on Figure 2-19. The Query Optimizer will estimate which operation
is less expensive: to sort the entire input and use a Stream Aggregate, or to use a Hash
Aggregate and sort the aggregated results.
SELECT ContactID , COUNT (*)
FROM Sales . SalesOrderHeader
GROUP BY ContactID
ORDER BY ContactID
Listing 2-22.