Databases Reference
In-Depth Information
Figure 2-15:
Stream Aggregate using an existing index.
The Sort operator is not needed in this plan, as the Clustered Index Scan provides
the data already sorted by
SalesOrderID
, which is part of the clustering key of the
SalesOrderDetail
table. As in the previous example, the Stream Aggregate operator
will consume the sorted data, but this time it will calculate the sum of the
LineTotal
column for each group.
Since the purpose of the Stream Aggregate operator is to aggregate values based on
groups, its algorithm relies on the fact that its input is already sorted by the
GROUP
BY
clause, and thus records from the same group are next to each other. Basically, in this
algorithm, the first record read will create the first group, and its aggregate value will
be initialized. Any record read after that will be checked to see if it matches the current
group; if it does match, then the record value will be aggregated to this group. On the
other hand, if the record doesn't match the current group, a new group will be created,
and its own aggregated value initialized. This process will continue until all the records
are processed.
Hash Aggregate
Now let us take a look at the Hash Aggregate operator, shown as Hash Match (Aggregate)
on the execution plans. This chapter describes two hash algorithms, Hash Aggregate and
Hash Join, which work in a similar way and are, in fact, implemented by the same physical
operator:
Hash Match
. I will cover the Hash Aggregate operator in this section, and the
Hash Join operator in the next one.
