Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.
Caution |
---|
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators. |
Applies to:
WITH ( <table_hint> [ [, ]...n ] ) <table_hint> ::= [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ] | FORCESCAN | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SPATIAL_WINDOW_MAX_CELLS = integer | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } <table_hint_limited> ::= { KEEPIDENTITY | KEEPDEFAULTS | HOLDLOCK | IGNORE_CONSTRAINTS | IGNORE_TRIGGERS | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.
All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.
Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.
If a table contains computed columns that are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables and are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.
SQL Server does not allow for more than one table hint from each of the following groups for each table in the FROM clause:
-
Granularity hints: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, or TABLOCKX.
-
Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.
Filtered Index Hints
A filtered index can be used as a table hint, but will cause the query optimizer to generate error 8622 if it does not cover all of the rows that the query selects. The following is an example of an invalid filtered index hint. The example creates the filtered index FIBillOfMaterialsWithComponentID and then uses it as an index hint for a SELECT statement. The filtered index predicate includes data rows for ComponentIDs 533, 324, and 753. The query predicate also includes data rows for ComponentIDs 533, 324, and 753 but extends the result set to include ComponentIDs 855 and 924, which are not in the filtered index. Therefore, the query optimizer cannot use the filtered index hint and generates error 8622. For more information, seeCreate Filtered Indexes.
USE AdventureWorks2012; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'FIBillOfMaterialsWithComponentID' AND object_id = OBJECT_ID(N'Production.BillOfMaterials')) DROP INDEX FIBillOfMaterialsWithComponentID ON Production.BillOfMaterials; GO CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID" ON Production.BillOfMaterials (ComponentID, StartDate, EndDate) WHERE ComponentID IN (533, 324, 753); GO SELECT StartDate, ComponentID FROM Production.BillOfMaterials WITH( INDEX (FIBillOfMaterialsWithComponentID) ) WHERE ComponentID in (533, 324, 753, 855, 924); GO
The query optimizer will not consider an index hint if the SET options do not have the required values for filtered indexes. For more information, seeCREATE INDEX (Transact-SQL).
Using NOEXPAND
NOEXPAND applies only to indexed views. An indexed view is a view with a unique clustered index created on it. If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. This functionality is calledindexed view matching. Automatic use of an indexed view by the query optimizer is supported only in specific editions of SQL Server. For a list of features that are supported by the editions of SQL Server, seeFeatures Supported by the Editions of SQL Server 2012.
However, for the optimizer to consider indexed views for matching, or use an indexed view that is referenced with the NOEXPAND hint, the following SET options must be set to ON.
ANSI_NULLS | ANSI_WARNINGS | CONCAT_NULL_YIELDS_NULL |
ANSI_PADDING | ARITHABORT1 | QUOTED_IDENTIFIERS |
1 ARITHABORT is implicitly set to ON when ANSI_WARNINGS is set to ON. Therefore, you do not have to manually adjust this setting.
Also, the NUMERIC_ROUNDABORT option must be set to OFF.
To force the optimizer to use an index for an indexed view, specify the NOEXPAND option. This hint can be used only if the view is also named in the query. SQL Server does not provide a hint to force a particular indexed view to be used in a query that does not name the view directly in the FROM clause; however, the query optimizer considers using indexed views, even if they are not referenced directly in the query.
Using a Table Hint as a Query Hint
Table hints can also be specified as a query hint by using the OPTION (TABLE HINT) clause. We recommend using a table hint as a query hint only in the context of aplan guide. For ad-hoc queries, specify these hints only as table hints. For more information, seeQuery Hints (Transact-SQL).
A. Using the TABLOCK hint to specify a locking method
The following example specifies that a shared lock is taken on the Production.Product table and is held until the end of the UPDATE statement.
USE AdventureWorks2012; GO UPDATE Production.Product WITH (TABLOCK) SET ListPrice = ListPrice * 1.10 WHERE ProductNumber LIKE 'BK-%'; GO
B. Using the FORCESEEK hint to specify an index seek operation
The following example uses the FORCESEEK hint without specifying an index to force the query optimizer to perform an index seek operation on theSales.SalesOrderDetail table.
USE AdventureWorks2012; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
The following example uses the FORCESEEK hint with an index to force the query optimizer to perform an index seek operation on the specified index and index column.
USE AdventureWorks2012; GO SELECT h.SalesOrderID, h.TotalDue, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID))) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
C. Using the FORCESCAN hint to specify an index scan operation
The following example uses the FORCESCAN hint to force the query optimizer to perform a scan operation on theSales.SalesOrderDetail table.
USE AdventureWorks2012; GO SELECT h.SalesOrderID, h.TotalDue, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESCAN) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);