Specifies that the indicated query hint should be used throughout the entire query. Each query hint can be specified only one time, although multiple query hints are permitted. Only one OPTION clause can be specified with the statement.
This clause can be specified in the SELECT, DELETE, UPDATE and MERGE statements.
Transact-SQL Syntax Conventions
-- Syntax for SQL Server and Azure SQL Database
[ OPTION ( <query_hint> [ ,...n ] ) ]
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
OPTION ( <query_option> [ ,...n ] )
<query_option> ::=
LABEL = label_name |
<query_hint>
<query_hint> ::=
HASH JOIN
| LOOP JOIN
| MERGE JOIN
| FORCE ORDER
| { FORCE | DISABLE } EXTERNALPUSHDOWN
query_hint
Keywords that indicate which optimizer hints are used to customize the way the Database Engine processes the statement. For more information, see Query Hints (Transact-SQL).
The following example shows how the OPTION
clause is used with a GROUP BY
clause.
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
The following example shows a simple SQL Data Warehouse SELECT statement with a label in the OPTION clause.
-- Uses AdventureWorks
SELECT * FROM FactResellerSales
OPTION ( LABEL = 'q17' );
The following example shows a SELECT statement that uses a HASH JOIN query hint in the OPTION clause.
-- Uses AdventureWorks
SELECT COUNT (*) FROM dbo.DimCustomer a
INNER JOIN dbo.FactInternetSales b
ON (a.CustomerKey = b.CustomerKey)
OPTION (HASH JOIN);
The following example is a SQL Data Warehouse SELECT statement that contains a label and multiple query hints. When the query is run on the Compute nodes, SQL Data Warehouse SQL Server will apply a hash join or merge join, according to the strategy that SQL Data Warehouse SQL Server SQL Server decides is the most optimal.
-- Uses AdventureWorks
SELECT COUNT (*) FROM dbo.DimCustomer a
INNER JOIN dbo.FactInternetSales b
ON (a.CustomerKey = b.CustomerKey)
OPTION ( Label = 'CustJoin', HASH JOIN, MERGE JOIN);
The following example creates a view named CustomerView and then uses a HASH JOIN query hint in a query that references a view and a table.
-- Uses AdventureWorks
CREATE VIEW CustomerView
AS
SELECT CustomerKey, FirstName, LastName FROM ssawPDW..DimCustomer;
SELECT COUNT (*) FROM dbo.CustomerView a
INNER JOIN dbo.FactInternetSales b
ON (a.CustomerKey = b.CustomerKey)
OPTION (HASH JOIN);
DROP VIEW CustomerView;
The following example shows a query that contains both a subselect and a query hint. The query hint is applied globally. Query hints are not allowed to be appended to the subselect statement.
-- Uses AdventureWorks
CREATE VIEW CustomerView AS
SELECT CustomerKey, FirstName, LastName FROM ssawPDW..DimCustomer;
SELECT * FROM (
SELECT COUNT (*) AS a FROM dbo.CustomerView a
INNER JOIN dbo.FactInternetSales b
ON ( a.CustomerKey = b.CustomerKey )) AS t
OPTION (HASH JOIN);
The following example uses the FORCE ORDER hint to force the query plan to use the join order specified by the query. This will improve performance on some queries; not all queries.
-- Uses AdventureWorks
-- Obtain partition numbers, boundary values, boundary value types, and rows per boundary
-- for the partitions in the ProspectiveBuyer table of the ssawPDW database.
SELECT sp.partition_number, prv.value AS boundary_value, lower(sty.name) AS boundary_value_type, sp.rows
FROM sys.tables st JOIN sys.indexes si ON st.object_id = si.object_id AND si.index_id <2
JOIN sys.partitions sp ON sp.object_id = st.object_id AND sp.index_id = si.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = si.data_space_id
JOIN sys.partition_range_values prv ON prv.function_id = ps.function_id
JOIN sys.partition_parameters pp ON pp.function_id = ps.function_id
JOIN sys.types sty ON sty.user_type_id = pp.user_type_id AND prv.boundary_id = sp.partition_number
WHERE st.object_id = (SELECT object_id FROM sys.objects WHERE name = 'FactResellerSales')
ORDER BY sp.partition_number
OPTION ( FORCE ORDER )
;
The following example forces the pushdown of the WHERE clause to the MapReduce job on the external Hadoop table.
SELECT ID FROM External_Table_AS A
WHERE ID < 1000000
OPTION (FORCE EXTERNALPUSHDOWN);
The following example prevents the pushdown of the WHERE clause to the MapReduce job on the external Hadoop table. All rows are returned to PDW where the WHERE clause is applied.
SELECT ID FROM External_Table_AS A
WHERE ID < 10
OPTION (DISABLE EXTERNALPUSHDOWN);
Hints (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)
MERGE (Transact-SQL)
DELETE (Transact-SQL)