SQL Server aggregate operator – Stream Aggregate Part2

Hi Geeks,

I hope you liked yesterday’s blog post on SQL Server Aggregate operator – Stream Aggregate Part1. Today, let us see what happens when we execute any query statement(s) using GROUP BY clause;

USE [AdventureWorks2012]

SELECT Product.Class,COUNT(*)
FROM [Production].[Product]
GROUP BY Product.Class

SAGroupByPlan

Stream aggregate operator requires its input to be sorted by GROUP BY clause and in our example Sort operator in the plan will provide data sorted by Product.Class column. Only when data is sorted, the records for the same group will be close to each other and stream aggregate operator can then count records for each group. This behaviour is different from the example used in part1 and reason behind this is, a query without GROUP BY clause considers entire input as a single group.

When we decode query plan in text format, it becomes very easy to understand on the working of this operator;

USE [AdventureWorks2012]

--Step 1
SET SHOWPLAN_TEXT ON

--Step 2
SELECT Product.Class,COUNT(*)
FROM [Production].[Product]
GROUP BY Product.Class

--Step 3
SET SHOWPLAN_TEXT OFF

SAGroupByTextPlan

Above image is self-explanatory and highlighted section says it all. In short, data is sorted by Product.Class column and then Stream Aggregate operator is able to count grouped data sets.

We are going to continue with this operator on tomorrow’s post.

Happy learning.

Regards,

Kanchan

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

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 *