Query Optimization and Execution - Pro SQL Server Internals - page 464

Listing 25-10. Stream Aggregate algorithm
/* Prerequirement: input is sorted */
for each row R1 from input
begin
if R1 does not match current group by criteria
begin
return current aggregate results (if any)
clear current aggregate results
set current group criteria to match R1
end
update aggregate results with R1 data
end
return current aggregate results (if any)
Due to the sorted input requirement, SQL Server often uses a stream aggregate together with the Sort operator.
Let's look at an example and create a table with some sales information for a company. After that, let's run the query,
which calculates total amount of sales for each customer. The code to perform this is shown in Listing 25-11.
Listing 25-11. Query that uses stream aggregate
create table dbo.Orders
(
OrderID int not null,
CustomerId int not null,
Total money not null,
constraint PK_Orders
primary key clustered(OrderID)
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N4)
insert into dbo.Orders(OrderId, CustomerId, Total)
select Num, Num % 10 + 1, Num
from Nums;
select Customerid, sum(Total) as [Total Sales]
from dbo.Orders
group by CustomerId
You can see the execution plan of the query in Figure 25-10 . There is no index on the CustomerId column, and
SQL Server needs to add a Sort operator to guarantee sorted input for the Stream Aggregate operator.
Figure 25-10. Execution plan of the query with Stream Aggregate