Microsoft SQL Server 2008
Microsoft SQL Server 2008

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: 2017-03-21

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 10

Matt BowlerDB team leadCommented: 2017-03-21

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

LVL 10

Matt BowlerDB team leadCommented: 2017-03-21

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.

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-03-23

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.