Avatar of hidrau
hidrau
Flag 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
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
hidrau

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Matt Bowler

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Matt Bowler

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
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
hidrau

ASKER
Thanks a lote
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes