About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Friday, February 13, 2015

Myth, Group by always result in sorting the table on Group by Column


There are lot threads on various forum discussing automatic sorting behaviour of Group By clause, lets put extra mile to prove this myth incorrect.

So here is my query, I am running this dummy query on AdventureWorks2012 database

select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc
inner join Production.ProductSubcategory psc
on pc.ProductCategoryID = psc.ProductCategoryID
group by pc.Name

Name                                               Sum of Sub Product Id
-------------------------------------------------- ---------------------
Accessories                                        378
Bikes                                              6
Clothing                                           172
Components                                         147

Let’s see from the prospective of execution plan,



From execution plan we can see the Group By physical operator is replaced by Stream Aggregate logical operator. And since Stream Operator always want its input to be in sorted order, we can see an existence of Sort operator in the execution plan which is sorting the ProductCategory data on ProductCategory.Name wise. And this is the reason why we get our result in sorted order when we use Group by clause our query.

Now just think about a situation where instead of Stream Aggregate, optimizer feels to use Hash Aggregate, which don’t require its input to be in sorted order.

Let’s try to bring Hash Operator in the plan in place of Stream Operator.

I am not able to stimulate the situation where Hash Aggregate appears in plan. So lets play a tricky game with Optimizer, and tell there is no such Stream aggregate operator present thus don’t make it use for creating plan

You can disable Stream Aggregate operator by

DBCC TRACEON (3604);
DBCC RULEOFF('GbAggToStrm');
GO

But later after your operation don’t forget to Rule on for Stream Aggregate operator.
DBCC TRACEON (3604);
DBCC RULEOFF('GbAggToStrm');
GO
select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc
inner join Production.ProductSubcategory psc
on pc.ProductCategoryID = psc.ProductCategoryID
group by pc.Name
OPTION (RECOMPILE);
GO
DBCC RULEON('GbAggToStrm');

Name                                               Sum of Sub Product Id
-------------------------------------------------- ---------------------
Bikes                                              6
Clothing                                           172
Accessories                                        378
Components                                         147

Here we can see the result is didn’t get sorted by optimizer, lets see how optimizer executed this query.



We can see the Stream Aggregate operator replaced by Hash Aggregate which don’t require its input to be in sorted manner and that is the reason why our result resulted in unsorted manner.

So to be in safe side, if you want your group by data in sorted manner do use order by clause along with group by, this way you can guarantee your data to be in sorted manner.
 
DBCC TRACEON (3604);
DBCC RULEOFF('GbAggToStrm');
GO
select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc
inner join Production.ProductSubcategory psc
on pc.ProductCategoryID = psc.ProductCategoryID
group by pc.Name
order by pc.Name
OPTION (RECOMPILE);
GO
DBCC RULEON('GbAggToStrm');

Name                                               Sum of Sub Product Id
-------------------------------------------------- ---------------------
Accessories                                        378
Bikes                                              6
Clothing                                           172
Components                                         147

Conclusion: Always use order by along with Group By, if you want your data to be in sorted manner.
 
SQL Server with a Tea glass makes a perfect combination to deal with darkness :)


Post Reference: Vikram Aristocratic Elfin Share