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

hidrau
hidrau used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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.

:)

Commented:
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ãoIT Engineer
Distinguished Expert 2017
Commented:
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.

Author

Commented:
Thanks a lote

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial