Stream Aggregate operator in SQL Server execution plan

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.