TSQL Execution Plan Operator - Stream Aggregate

The Stream Aggregate operator groups rows by one or more columns and then aggregate expressions calculated for the specified query. The most common types of aggregate functions are SUM, COUNT, SUM, AVG, MIN, MAX and when we execute these aggregation functions, the Stream Aggregate operator appears in the execution plan. In this video, we’ll be going to learn about how this operator works.

In this session, we’ll be going to learn about the next operator of the T-SQL execution plan called a Stream Aggregate. The Stream Aggregate operator group rows by one or more columns and then aggregate expressions calculated for the specified query. The most common types of aggregate functions are SUM, COUNT, AVG, MIN, and MAX functions. When we execute this aggregation function, the Stream Aggregate operator appears in the execution plan. Stream Aggregation is very fast because it requires an input that has already been sorted by the columns specified in the Group By clause. If the aggregated data is not sorted then Optimizer can use a Sort Operator to pre-sort the records.

A Stream Aggregate is divided into two types:

Scalar Aggregation
Group Aggregation.

The Scalar Aggregations are queries that use aggregation functions but do not have a Group By clause. A Group Aggregations are the queries that have a column specified in the Group By clause.

For this demo, let’s start off with this simple query.

– Scalar Aggregation

- SELECT COUNT (*) AS EmployeeCount

FROM HumanResources.Employee

Now, as I said earlier, Stream Aggregation is divided into two types, Scalar Aggregation and Group Aggregation. Let’s start Scalar Aggregation with this query where I’m retrieving the total number of employee records from the employee table. After execution, if we look at the execution plan, we can see the first step is to read all the rows from the employee table using the index scan.

Then we have a Stream Aggregate operator which performs the count start operation. After the count, we have the compute scalar operator and we are seeing this compute scalar operator because, after the count, the result of the count is placed into this expression. The compute scalar operator converts this expression to an integer data type.

Stream Aggregate Tutorial

Our next query is in conjunction with the Group By clause.

– Aggregation with SORT operator

- SELECT JobTitle, COUNT (*) AS EmployeeCount

FROM HumanResources.Employee

GROUP BY JobTitle

This query will return me the count of rows by employees’ job titles. Execute this query and now if we look at the execution plan, here again, SQL Server reads all the data from the employee table via this Clustered Index Scan. Then we have a Sort operator which sorts the records by the job title and SQL Server then starts the aggregation.

In our case, you can see the records are not shorted on the job title column. That’s why we have a Sort operator in our execution plan. Now to get rid of this Sort of operator, create the non-cluster index on the job title column.

Once again, when we execute this query and check the execution plan and this time you can see the Sort operator is gone. Our query is returning the first result in comparison with the previous query.

Stream Aggregate

It is because now the SQL server is taking the advantage of this index, which we have created above. It uses the order index by job title to perform the aggregation. So now, because the rows are already being shortened, SQL Server doesn’t need to sort the data. This is why in the execution plan, the index scan is immediately followed by our Stream Aggregate operator.

For more videos and articles visit:

Useful Links:

TSQL Execution Plan Operator - Stream Aggregate

For more videos and articles visit:

Useful Links:

👉 GET YOUR FREE API KEY for PDF.co

✅ ON-PREMISE SDK FREE TRIAL:

🔎 SDK DOCUMENTATION:

🔑 WEB API DOCUMENTATION :