As a SQL Server enthusiast, I always strive to address the questions and concerns raised by my readers. Today, I want to shed some light on a commonly encountered showplan operator called Stream Aggregate.

The Stream Aggregate operator is often found in simple COUNT(*) operations’ execution plans. This operator groups rows by one or more columns and calculates one or more aggregate expressions returned by the query.

To see the Stream Aggregate Operator in action, you can run the following query:

USE AdventureWorks
GO

SELECT COUNT(*) AS cRows
FROM HumanResources.Shift;
GO

If you enable the Execution Plan by pressing CTRL + M, you will notice the presence of the Stream Aggregate operator.

Additionally, you can view the Aggregate operation in text format by using the SET SHOWPLAN_ALL ON command:

SET SHOWPLAN_ALL ON;
GO

SELECT COUNT(*) AS cRows
FROM HumanResources.Shift;
GO

SET SHOWPLAN_ALL OFF;
GO

By examining the text representation of the execution plan, you can observe that the aggregation occurs due to the COUNT(*) operation.

Interestingly, the Compute Scalar operation is also present in the same operation. This operation performs the implicit conversion of the COUNT(*) operation. During the stream aggregation, the expression is converted to BIGINT and then back to INT after the COUNT(*) operation.

To demonstrate that the Stream Aggregate returns results in the BIGINT format, we can modify the code to use COUNT_BIG(*) instead of COUNT(*):

SET SHOWPLAN_ALL ON;
GO

SELECT COUNT_BIG(*) AS cRows
FROM HumanResources.Shift;
GO

SET SHOWPLAN_ALL OFF;
GO

By comparing the execution plans, you can observe that there is no Compute Scalar operation when using COUNT_BIG instead of COUNT.

Now, you might wonder if the performance of COUNT_BIG is better than the COUNT operation. To evaluate this, let’s compare the performance of both operations:

SET SHOWPLAN_ALL ON;
GO

SELECT COUNT(*) AS cRows
FROM HumanResources.Shift;
GO

SELECT COUNT_BIG(*) AS cRows
FROM HumanResources.Shift;
GO

SET SHOWPLAN_ALL OFF;
GO

From the execution plan, you will notice that the cost of both operations is exactly the same.

I would love to hear your thoughts on this article and your initial reaction to the behavior of SQL Server. Please leave a comment below.