OPTION 子句 (Transact-SQL)OPTION Clause (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

指定应在整个查询中使用所指定的查询提示。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. 使用该语句只能指定一个 OPTION 子句。Only one OPTION clause can be specified with the statement.

可以在 SELECT、DELETE、UPDATE 和 MERGE 语句中指定此子句。This clause can be specified in the SELECT, DELETE, UPDATE and MERGE statements.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- 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  

参数Arguments

query_hintquery_hint
关键字,指示优化器提示用于自定义数据库引擎处理语句的方式。Keywords that indicate which optimizer hints are used to customize the way the Database Engine processes the statement. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

示例Examples

A.A. 将 OPTION 子句与 GROUP BY 子句一起使用Using an OPTION clause with a GROUP BY clause

以下示例说明了如何将 OPTION 子句与 GROUP BY 子句一起使用。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  

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

B.B. 在 OPTION 子句中包含标签的 SELECT 语句SELECT statement with a label in the OPTION clause

以下示例显示在 OPTION 子句中包含标签的简单 SQL 数据仓库SQL Data Warehouse SELECT 语句。The following example shows a simple SQL 数据仓库SQL Data Warehouse SELECT statement with a label in the OPTION clause.

-- Uses AdventureWorks  
  
SELECT * FROM FactResellerSales  
  OPTION ( LABEL = 'q17' );  

C.C. 在 OPTION 子句中包含查询提示的 SELECT 语句SELECT statement with a query hint in the OPTION clause

以下示例显示了在 OPTION 子句中使用 HASH JOIN 查询提示的 SELECT 语句。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);  

D.D. 在 OPTION 子句中包含标签和多个查询提示的 SELECT 语句SELECT statement with a label and multiple query hints in the OPTION clause

以下示例是包含标签和多个查询提示的 SQL 数据仓库SQL Data Warehouse SELECT语句。The following example is a SQL 数据仓库SQL Data Warehouse SELECT statement that contains a label and multiple query hints. 查询在计算节点上运行时,SQL ServerSQL Server 会根据 SQL ServerSQL Server 决定的最优策略,应用哈希联接或合并联接。When the query is run on the Compute nodes, SQL ServerSQL Server will apply a hash join or merge join, according to the strategy that SQL ServerSQL 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);  

E.E. 查询视图时使用查询提示Using a query hint when querying a view

以下示例创建名为 CustomerView 的视图,然后在引用视图和表的查询中使用 HASH 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;  
  

F.F. 包含子查询和查询提示的查询Query with a subselect and a query hint

以下示例显示包含子查询和查询提示的查询。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);  

G.G. 强制联接顺序与查询中的顺序相匹配Force the join order to match the order in the query

以下示例使用 FORCE ORDER 提示强制查询计划使用查询指定的联接顺序。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 )  
;  

H.H. 使用 EXTERNALPUSHDOWNUsing EXTERNALPUSHDOWN

以下示例强制将 WHERE 子句下推到外部 Hadoop 表上的 MapReduce 作业。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);  

以下示例阻止将 WHERE 子句下推到外部 Hadoop 表上的 MapReduce 作业。The following example prevents the pushdown of the WHERE clause to the MapReduce job on the external Hadoop table. 所有行都返回到应用 WHERE 子句的 PDW。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);  

另请参阅See Also

提示 (Transact-SQL) Hints (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
MERGE (Transact-SQL) MERGE (Transact-SQL)
DELETE (Transact-SQL)DELETE (Transact-SQL)