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?
Thanks
alex
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
Thanks
alex
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
:)
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.
:)
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial