Link to home
Start Free TrialLog in
Avatar of hidrau
hidrauFlag for Brazil

asked on

What can the "OPTION" in the GROUP help me in my query?

I have this example, but as I don't have the database AdventureWorks2012, I'd like to know what does that OPTION serve for?

USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

another example

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

Open in new window


Thanks
alex
ASKER CERTIFIED SOLUTION
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There's a good piece on hashing here: https://blogs.msdn.microsoft.com/craigfr/2006/09/20/hash-aggregate/

Essentially it controls how the groups are matched.

As far as the merge union, merging is how the two sets are joined, merge implies a sorted output, where the optimiser may chose a purely concatenated output.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hidrau

ASKER

Thanks a lote