Many other execution plan iterators are available. In this section, three additional important iterators are introduced.

SQL Server uses the Sort operator whenever it has to sort an input. There might be many reasons to sort the input. For example, SQL Server might decide to sort an input so it can use the merge join algorithm. A very typical example of Sort operator usage is for queries that request an ordered rowset when the order is not supported by an index. The sort operation could be very expensive; for good performance, you should make sure that the Sort operator is used for small inputs only. The following query requests an ordered rowset. The rowset should be ordered by the qty column of the Sales.OrderDetails table. However, the table has no index on this column.
SELECT orderid, productid, qty FROM Sales.OrderDetails ORDER BY qty;
Figure 17-11 shows the execution plan for this query. Note that the cost of the Sort operator is around 81 percent of the total query cost.


Figure 17-11 The Sort iterator.

SQL Server uses two different algorithms for calculating aggregations. If an input is ordered by the columns used in the GROUP BY clause, then SQL Server uses the stream aggregation algorithm, which is implemented in the Stream Aggregate operator. Stream aggregation is very efficient. SQL Server might even decide to sort the input before performing the aggregation in order to make it possible to use the Stream Aggregate operator.
The following query uses the Stream Aggregate operator. Note that it groups rows from the Sales.OrderDetails table by the productid column. A nonclustered index over this column exists. In addition, because the query does not require any other column, the index is covering.
SELECT productid, COUNT(*) AS num
FROM Sales.OrderDetails
GROUP BY productid ;
Figure 17-12 shows the execution plan for this query.


Figure 17-12 The Stream Aggregate iterator.

If the input for the aggregation is not ordered and the input is so big that sorting it would be inefficient, then SQL Server uses the hash aggregation algorithm. The operator used for this kind of aggregation is the Hash Match Aggregate operator. The icon is the same as the icon for the Hash Match Join operator. The hash aggregation algorithm builds the hash table from the input like it builds it for a hash join. However, the buckets are used to store the groups.
Similarly to a hash join, hash aggregation is scalable as well. Like the stream aggregation algorithm, the hash aggregation algorithm can compute multiple groups simultaneously in multiple threads. The following query groups rows from the Sales.OrderDetails table by the qty column; the aggregation is not supported by an index.
SELECT qty, COUNT(*) AS num
FROM Sales.OrderDetails
Figure 17-13 shows the execution plan for this query. Note that SQL Server used the Hash Match (Aggregate) operator. Note also that the relative hash aggregation cost is much higher than the stream aggregation shown earlier in Figure 17-12. While the stream aggregation contributed approximately 14 percent to the total cost of the query, the hash aggregation contributed approximately 71 percent.


Figure 17-13 The Hash Match (Aggregate) iterator.