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.