SQL Server aggregate operator – Stream Aggregate Part3

Hi Friends,

I hope you liked previous blog posts on SQL Server aggregate operator – Stream Aggregate Part1 and Part2. In today’s post, we are going to see that this operator can also use an index to have its input sorted.

USE [AdventureWorks2012]

SELECT SalesOrderDetail.SalesOrderID,SUM(SalesOrderDetail.LineTotal) AS 'SUM'
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderDetail.SalesOrderID

 

SAUsingIndexPlan

There is no need to have sort operation as clustered index scan produce the data sorted by SalesOrderDetail.SalesOrderID column. Stream aggregate operator will consume sorted data and it will calculate sum of SalesOrderDetail.LineTotal column group wise. We can have more clarity when query plan is decoded in text.

USE [AdventureWorks2012]

--Step 1
SET SHOWPLAN_TEXT ON

--Step 2
SELECT SalesOrderDetail.SalesOrderID,SUM(SalesOrderDetail.LineTotal) AS 'SUM'
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderDetail.SalesOrderID

--Step 3
SET SHOWPLAN_TEXT OFF

SAUsingIndexTextPlan

Stream Aggregate operator aggregate values based on groups and its logic relies on the fact that input is being sorted using GROUP BY. Algorithm is designed to create a group based on first record then and then initialize aggregate values. Next record is checked to see if they match first group and if so then include it in the first group else create a new one and this process continues until all records are being processed.

I hope this explains how stream aggregate works. I’ll be back tomorrow with another operator, stay tuned!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *