<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 { UNKNOWN | = literal_constant } [ , ...n ] ) | OPTIMIZE FOR UNKNOWN | PARAMETERIZATION { SIMPLE | FORCED } | RECOMPILE | ROBUST PLAN | KEEP PLAN | KEEPFIXED PLAN | EXPAND VIEWS | MAXRECURSION number | USE PLAN N'xml_plan' | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] ) <table_hint> ::= [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FASTFIRSTROW | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
A. Using MERGE JOIN The following example specifies that the JOIN operation in the query is performed by MERGE JOIN. Copy Code USE AdventureWorks2008R2; GO SELECT * FROM Sales.Customer AS c INNER JOIN Sales.vStoreWithAddresses AS sa ON c.CustomerID = sa.BusinessEntityID 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 and to use statistical data to determine the value for the local variable @postal_code when optimizing the query. Copy Code USE AdventureWorks2008R2; GO DECLARE @city_name nvarchar(30); DECLARE @postal_code nvarchar(15); SET @city_name = 'Ascheim'; SET @postal_code = 86171; SELECT * FROM Person.Address WHERE City = @city_name AND PostalCode = @postal_code OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) ); 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. Copy Code USE AdventureWorks2008R2; GO --Creates an infinite loop WITH cte (CustomerID, PersonID, StoreID) AS ( SELECT CustomerID, PersonID, StoreID FROM Sales.Customer WHERE PersonID IS NOT NULL UNION ALL SELECT cte.CustomerID, cte.PersonID, cte.StoreID FROM cte JOIN Sales.Customer AS e ON cte.PersonID = e.CustomerID ) --Uses MAXRECURSION to limit the recursive levels to 2 SELECT CustomerID, PersonID, StoreID FROM cte OPTION (MAXRECURSION 2); GO After the coding error is corrected, MAXRECURSION is no longer required. D. Using MERGE UNION The following example uses the MERGE UNION query hint. Copy Code USE AdventureWorks2008R2; GO SELECT * FROM HumanResources.Employee AS e1 UNION SELECT * FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO E. Using HASH GROUP and FAST The following example uses the HASH GROUP and FAST query hints. Copy Code USE AdventureWorks2008R2; 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. Copy Code USE AdventureWorks2008R2 ; 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 G. Using INDEX The following examples use the INDEX hint. The first example specifies a single index. The second example specifies multiple indexes for a single table reference. In both examples, because the INDEX hint is applied on a table that uses an alias, the TABLE HINT clause must also specify the same alias as the exposed object name. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide1', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE e.OrganizationLevel = 2;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))'; GO EXEC sp_create_plan_guide @name = N'Guide2', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE e.OrganizationLevel = 2;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))'; GO H. Using FORCESEEK The following example uses the FORCESEEK table hint. Because the INDEX hint is applied on a table that uses a two-part name, the TABLE HINT clause must also specify the same two-part name as the exposed object name. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide3', @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle FROM HumanResources.Employee JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID WHERE HumanResources.Employee.OrganizationLevel = 3 ORDER BY c.LastName, c.FirstName;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))'; GO I. Using multiple table hints The following example applies the INDEX hint to one table and the FORCESEEK hint to another. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide4', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE OrganizationLevel = 3;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) , TABLE HINT ( c, FORCESEEK) )'; GO J. Using TABLE HINT to override an existing table hint The following example shows how to use the TABLE HINT hint without specifying a hint to override the behavior of the INDEX table hint specified in the FROM clause of the query. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide5', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode)) JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE OrganizationLevel = 3;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT(e))'; GO K. Specifying semantics-affecting table hints The following example contains two table hints in the query: NOLOCK, which is semantic-affecting, and INDEX, which is non-semantic-affecting. To preserve the semantics of the query, the NOLOCK hint is specified in the OPTIONS clause of the plan guide. In addition to the NOLOCK hint, the INDEX and FORCESEEK hints are specified and replace the non-semantic-affecting INDEX hint in the query when the statement is compiled and optimized. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide6', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE OrganizationLevel = 3;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))'; GO The following example shows an alternative method to preserving the semantics of the query and allowing the optimizer to choose an index other than the index specified in the table hint. This is done by specifying the NOLOCK hint in the OPTIONS clause (because it is semantic-affecting) and specifying the TABLE HINT keyword with only a table reference and no INDEX hint. Copy Code USE AdventureWorks2008R2; GO EXEC sp_create_plan_guide @name = N'Guide7', @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle FROM HumanResources.Employee AS e JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID WHERE OrganizationLevel = 2;', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT ( e, NOLOCK))'; GO