SQL Server hash match aggregate operator – Part3

Hi Friends,

I hope you liked SQL Server hash match aggregate Part1 and Part2. Today we are going to see if input data is not sorted and we explicitly specify the order in query, what is the behaviour of the query optimizer.

USE [AdventureWorks2012]

--Step1
DROP INDEX Sales.SalesOrderHeader.IX_TerritoryID

--Step2
SELECT SalesOrderHeader.TerritoryID, COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY SalesOrderHeader.TerritoryID
ORDER BY SalesOrderHeader.TerritoryID

SQL Server query optimizer decided to use a Hash Match aggregate then sort results by TerritoryID column. Query optimizer could have chosen a Stream aggregate and sort but as it is a cost based decision, whichever is less expensive is going to be the choice of optimizer.

That’s all for today, see you tomorrow on One operator a day series.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook