Query Hint (Transact-SQL)

Specifies that the indicated query hint should be used throughout the query. The query hint affects all operators in the statement. If UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause. Query hints are specified as part of the OPTION Clause. If one or more query hints causes the query optimizer not to generate a valid plan, error 8622 is raised.

Applies to:

DELETE

INSERT

SELECT

UPDATE

Transact-SQL Syntax Conventions

Syntax

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] ) 
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
} 

Arguments

• { HASH |ORDER } GROUP
  Specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing or ordering.

• { MERGE |HASH |CONCAT } UNION
  Specifies that all UNION operations are performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.

  Note

  If a <joint_hint> is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any <join_hint> specified in the OPTION clause.

• { LOOP | MERGE | HASH } JOIN
  Specifies that all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. If more than one join hint is specified, the optimizer selects the least expensive join strategy from the allowed ones.

  If, in the same query, a join hint is also specified for a specific pair of tables, this join hint takes precedence in the joining of the two tables, although the query hints still must be honored. Therefore, the join hint for the pair of tables may only restrict the selection of allowed join methods in the query hint. For more information, see Hints (Transact-SQL).

• FAST number_rows
  Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

• FORCE ORDER
  Specifies that the join order indicated by the query syntax is preserved during query optimization.

  Note

  Using FORCE ORDER does not affect possible role reversal behavior of the query optimizer. For more information, see Understanding Hash Joins.

  For information about how the SQL Server query optimizer enforces the FORCE ORDER hint when a query contains a view, see View Resolution.

• MAXDOP number
  Overrides the max degree of parallelism configuration option of sp_configure for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see max degree of parallelism Option.

• OPTIMIZE FOR
  Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. OPTIMIZE FOR can counteract the parameter detection behavior of the optimizer or can be used when you create plan guides. For more information, see Recompiling Stored Procedures and Optimizing Queries in Deployed Applications by Using Plan Guides.

• @variable_name
  Is the name of a local variable used in a query, to which a value may be assigned for use with the OPTIMIZE FOR query hint.

• literal_constant
  Is a literal constant value to be assigned @variable_name for use with the OPTIMIZE FOR query hint. literal_constant is used only during query optimization, and not as the value of @variable_name during query execution. literal_constant can be of any SQL Server system data type that can be expressed as a literal constant. The data type of literal_constant must be implicitly convertible to the data type that @variable_name references in the query.

• ,…n
  Indicates that more than one @variable_name can be assigned a literal_constant for use with the OPTIMIZE FOR query hint.

• PARAMETERIZATION { SIMPLE | FORCED }
  Specifies the parameterization rules that the SQL Server query optimizer applies to the query when it is compiled.

  Important

  The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.

  SIMPLE instructs the query optimizer to attempt Simple Parameterization. FORCED instructs the optimizer to attempt Forced Parameterization. The PARAMETERIZATION query hint is used to override the current setting of the PARAMETERIZATION database SET option inside a plan guide. For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.

• RECOMPILE
  Instructs the SQL Server 2005 Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

  RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see Recompiling Stored Procedures. RECOMPILE is also useful when you create plan guides. For more information, see Optimizing Queries in Deployed Applications by Using Plan Guides.

• ROBUST PLAN
  Forces the query optimizer to try a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may have to store and process rows that are wider than any one of the input rows. The rows may be so wide that, sometimes, the particular operator cannot process the row. If this occurs, the Database Engine produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.

  If such a plan is not possible, the query optimizer returns an error instead of deferring error detection to query execution. Rows may contain variable-length columns; the Database Engine allows for rows to be defined that have a maximum potential size beyond the ability of the Database Engine to process them. Generally, despite the maximum potential size, an application stores rows that have actual sizes within the limits that the Database Engine can process. If the Database Engine encounters a row that is too long, an execution error is returned.

• KEEP PLAN
  Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.

• KEEPFIXED PLAN
  Forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.

• EXPAND VIEWS
  Specifies that the indexed views are expanded and the query optimizer will not consider any indexed view as a substitute for any part of the query. A view is expanded when the view name is replaced by the view definition in the query text.

  This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

  The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ) is specified. For more information about the query hint WITH (NOEXPAND), see FROM (Transact-SQL).

  Only the views in the SELECT part of statements, including those in INSERT, UPDATE, and DELETE statements are affected by the hint.

• MAXRECURSION number
  Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

  When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

  Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

  For more information, see WITH common_table_expression (Transact-SQL).

• USE PLAN N**'xml_plan'**
  Forces the query optimizer to use an existing query plan for a query that is specified by 'xml_plan'. For more information, see Specifying Query Plans with Plan Forcing. USE PLAN cannot be specified with INSERT, UPDATE, or DELETE statements.

Remarks

Query hints cannot be specified in an INSERT statement except when a SELECT clause is used inside the statement.

Query hints can be specified only in the top-level query, not in subqueries.

Examples

A. Using MERGE JOIN

The following example specifies that the JOIN operation in the query is performed by MERGE JOIN.

USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

B. Using OPTIMIZE FOR

The following example instructs the query optimizer to use the value 'Seattle' for local variable @city_name when optimizing the query.

DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
GO

C. Using MAXRECURSION

MAXRECURSION can be used to prevent a poorly formed recursive common table expression from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

After the coding error is corrected, MAXRECURSION is no longer required.

D. Using UNION

The following example uses the MERGE UNION query hint.

USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO

E. Using HASH GROUP and FAST

The following example uses the HASH GROUP and FAST query hints.

USE AdventureWorks ;
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

F. Using MAXDOP

The following example uses the MAXDOP query hint.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

See Also

Reference

Hints (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History