Share via

Stream Aggregate Showplan Operator

  • Article

The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query. The output of this operator can be referenced by later operators in the query, returned to the client, or both. The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan. In the SHOWPLAN_ALL statement or the graphical execution plan in SQL Server Management Studio, the columns in the GROUP BY predicate are listed in the Argument column, and the aggregate expressions are listed in the DefinedValues column.

Stream Aggregate is a physical operator.

Stream aggregate operator iconGraphical execution plan icon

Examples

The following example selects data from the SalesPerson table and aggregates the values for the Bonus and SalesYTD columns by territory. The ouput of the execution plan shows that the query optimizer uses the Stream Aggregate operator to group the rows in the SalesPerson table by the TerritoryID column and then calculate the values for the AVG(Bonus) and SUM(SalesYTD) columns based on that grouping.

USE AdventureWorks;
GO
SET NOCOUNT ON;
go
SET SHOWPLAN_ALL ON;
GO
SELECT TerritoryID, 
    AVG(Bonus) AS 'Average bonus', 
    SUM(SalesYTD) AS'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
SET SHOWPLAN_ALL OFF;

The execution plan output of the Stream Aggregate operator appears below.

StmtText 
------------------------------------------------------------------------------------------------
|--Stream Aggregate(GROUP BY:([AdventureWorks].[Sales].[SalesPerson].[TerritoryID]) )
        DEFINE:([Expr1011]=Count(*), 
                [Expr1012]= SUM([AdventureWorks].[Sales].[SalesPerson].[Bonus]), 
                [Expr1004]=SUM([AdventureWorks].[Sales].[SalesPerson].[SalesYTD])))

Argument
-----------------------------------------------------------------------------------------------
GROUP BY:([AdventureWorks].[Sales].[SalesPerson].[TerritoryID]

DefinedValues
----------------------------------------------------------------------------------------------
[Expr1011]=Count(*), 
[Expr1012]=SUM([AdventureWorks].[Sales].[SalesPerson].[Bonus]),
[Expr1004]=SUM([AdventureWorks].[Sales].[SalesPerson].[SalesYTD])

See Also

Tasks

How to: Display an Actual Execution Plan

Concepts

Logical and Physical Operators Reference
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

Other Resources

Aggregate Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the Examples section.
Updated content:
  • Clarified the description of the Stream Aggregate operator.