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
LVL 1
hidrauAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt BowlerDB team leadCommented:
The option clause applies to the query as a whole rather than the GROUP BY.

All of the options are detailed here.

https://msdn.microsoft.com/en-us/library/ms181714.aspx

Hash forces the group by aggregates to use hashing. Fast 10 optimises the query to return the first 10 rows quickly and merge union says that the two sets should be merged.

:)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt BowlerDB team leadCommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The OPTION gives you the possibility to change the default behavior for a SELECT statement. You can say how the engine will use a JOIN or an INDEX, for example.
It shouldn't be used if you don't know how it will change a query's behavior. Only for advanced users that knows what they are doing.
Before using this in a PROD environment I highly recommend you to perform a lot of tests in a development environment and only when you feel comfortable with this you should use it in Production.
hidrauAuthor Commented:
Thanks a lote
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.