Microsoft SQL Server Query Hints GROUP BY Hints
Example
When you use GROUP BY clause, SQL Server query optimizer (QO) can choose different types of grouping operators:
- HASH Aggregate that creates hash-map for grouping entries
- Stream Aggregate that works well with pre-ordered inputs
You can explicitly require that QO picks one or another aggregate operator if you know what would be the optimal. With OPTION (ORDER GROUP), QO will always choose Stream aggregate and add Sort operator in front of Stream aggregate if input is not sorted:
select OrderID, AVG(Quantity)
from Sales.OrderLines
group by OrderID
OPTION (ORDER GROUP)
With OPTION (HASH GROUP), QO will always choose Hash aggregate :
select OrderID, AVG(Quantity)
from Sales.OrderLines
group by OrderID
OPTION (HASH GROUP)
