In the query when we are using any aggregate function like SUM (), MIN (), MAX (), COUNT (), AVG () etc. Stream Aggregate operator appear in the execution plan. We can say that The Stream Aggregate Operator in SQL Server execution plans is used to perform aggregations on a sorted stream of data. The Stream Aggregate operator processes data in a row-by-row fashion, assuming that the input is sorted. It is highly efficient when the data is pre-sorted because it can process rows in a streaming manner without needing to buffer the data.
Icon of Stream Aggregate Operator
See the example
|
select MAX(StandardCost) as MAX_StandardCost from Production.Product |
See the property.
See the other example
AVG function:
|
select AVG(StandardCost) as MAX_StandardCost from Production.Product |
Here we are seeing stream Aggregate & compute scalar operator.
Read: compute scalar operator
https://bageshkumarbagi-msbi.blogspot.com/2024/11/compute-scalar-operator-in-sql-server.html
If we see the property of both
operator than we come to know why both this plan use both operator.
See in the Expr1003 getting the
count and Expr1004 calculating sum as we saw this operator do not calculate the
average.
|
Scalar Operator(CASE WHEN [Expr1003]=(0) THEN NULL ELSE [Expr1004]/CONVERT_IMPLICIT(money,[Expr1003],0) END) |
In compute scalar calculating avg. The Stream Aggregate Operator in SQL Server execution plans is used to perform aggregations on sorted data, such as when using aggregate functions like SUM(), COUNT(), and AVG(), or when using GROUP BY.
