Table Hint (Transact-SQL)

Specifies that the query optimizer use a table scan, one or more indexes, or a locking method with this table or view and for this SELECT, INSERT, UPDATE, or DELETE statement. Although this is an option, the query optimizer can generally pick the best optimization method without hints being specified.

Applies to:

DELETE

INSERT

SELECT

UPDATE

Transact-SQL Syntax Conventions

Syntax

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW 
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOWAIT
    | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

Arguments

• NOEXPAND
  Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views. For more information, see Remarks.

• INDEX ( index_val [ ,... n ] )
  Specifies the name or ID of the indexes to be used by the query optimizer when it processes the statement. Only one index hint per table can be specified.

  If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.

  The alternative INDEX = syntax specifies a single index hint. This is supported only for backward compatibility.

  If multiple indexes are used in the single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing and the query optimizer applies as many conditions as possible on each index accessed. If the collection of hinted indexes is not covering, a fetch is performed after the SQL Server 2005 Database Engine retrieves all the indexed columns.

  Note

  When an index hint referring to multiple indexes is used on the fact table in a star join, the optimizer ignores the index hint and returns a warning message. Also, index ORing is not allowed for a table with an index hint specified.

  The maximum number of indexes in the table hint is 250 nonclustered indexes.

• KEEPIDENTITY
  Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

  Specifies that identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and the query optimizer automatically assigns unique values based on the seed and increment values specified during table creation.

  Important

  If the data file does not contain values for the identity column in the table or view, unless the identity column is the last column in the table, you must skip the identity column. For more information, see Using a Format File to Skip a Data Field. If an identity column is successfully skipped, the query optimizer automatically assigns unique values for the identity column into the imported table rows.

  For an example that uses this hint in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, see Keeping Identity Values When Bulk Importing Data.

  For information about checking the identity value for a table, see DBCC CHECKIDENT (Transact-SQL).

• KEEPDEFAULTS
  Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

  Specifies to insert a table column's default value, if any, instead of NULL when the data record lacks a value for the column.

  For an example that uses this hint in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, see Keeping Nulls or Using Default Values During Bulk Import.

• FASTFIRSTROW
  Is equivalent to OPTION (FAST 1). For more information, see FAST in the OPTION clause in SELECT.

• HOLDLOCK
  Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

• IGNORE_CONSTRAINTS
  Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

  Specifies that any constraints on the table are ignored by the bulk-import operation. By default, INSERT checks CHECK and FOREIGN KEY constraints. When IGNORE_CONSTRAINTS is specified for a bulk-import operation, INSERT must ignore these constraints on a target table. Note that you cannot disable UNIQUE, PRIMARY KEY, or NOT NULL constraints.

  A situation in which you might want to disable CHECK and FOREIGN KEY constraints is if the input data contains rows that violate constraints. By disabling the CHECK and FOREIGN KEY constraints, you can import the data and then use Transact-SQL statements to clean up the data.

  However, note when CHECK and FOREIGN KEY constraints are ignored, after the operation each ignored constraint on the table is marked as is_not_trusted in the sys.check_constraints or sys.foreign_keys catalog view. At some point, you will have to check the constraints on the whole table. If the table was not empty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK and FOREIGN KEY constraints to the incremental data.

• IGNORE_TRIGGERS
  Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

  Specifies that any triggers defined on the table are ignored by the bulk-import operation. By default, INSERT applies triggers.

  Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.

• NOLOCK
  Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

• NOWAIT
  Instructs the SQL Server 2005 Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.

• PAGLOCK
  Takes page locks either where individual locks are ordinarily taken on rows or keys, or where a single table lock is ordinarily taken. By default, uses the lock mode appropriate for the operation. When specified in transactions operating at the SNAPSHOT isolation level, page locks are not taken unless PAGLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

• READCOMMITTED
  Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. If the database option READ_COMITTED_SNAPSHOT is OFF, the Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, the Database Engine does not acquire locks and uses row versioning. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• READCOMMITTEDLOCK
  Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. The Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• READPAST
  Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. The Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released. READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK. When READPAST is specified both row-level and page-level locks are skipped. READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. READPAST cannot be specified for tables in the INTO clause of an INSERT statement.

  Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

  For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

• READUNCOMMITTED
  Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, or present users with data that was never committed.

  READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).

  READUNCOMMITTED, and NOLOCK, cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

  Note

  Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of Microsoft SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

  In SQL Server 2005 you can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:

  • The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON.
  • The SNAPSHOT isolation level.

  For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  Note

  If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.

• REPEATABLEREAD
  Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• ROWLOCK
  Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

• SERIALIZABLE
  Is equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• TABLOCK
  Specifies that a lock is taken on the table and held until the end-of-statement. If data is being read, a shared lock is taken. If data is being modified, an exclusive lock is taken. If HOLDLOCK is also specified, a shared table lock is held until the end of the transaction.

  When used with the OPENROWSET bulk rowset provider to import data into a table without indexes, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking.

• TABLOCKX
  Specifies that an exclusive lock is taken on the table until the transaction completes.

• UPDLOCK
  Specifies that update locks are to be taken and held until the transaction completes.

• XLOCK
  Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

Remarks

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.

Using commas between table hints is optional but encouraged. Separating hints by spaces rather than commas is supported for backward compatibility.

In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:

FROM t (FASTFIRSTROW)

When the hint is specified with another option, the hint must be specified with the WITH keyword:

FROM t WITH (FASTFIRSTROW, INDEX(myindex))

The restrictions apply when the hints are used in queries against databases with the compatibility level of 90.

In SQL Server 2005, all lock hints are propagated to all the tables and views that are 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 and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints 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, ROWLOCK, TABLOCK, or TABLOCKX.
• Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

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 function is called indexed view matching, and is supported only in the SQL Server 2005 Enterprise and Developer editions.

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:

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

For more information, see Resolving Indexes on Views.

Permissions

The KEEPIDENTITY, IGNORE_CONSTRAINTS, and IGNORE_TRIGGERS hints require ALTER permissions on the table.

Examples

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.

UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%'

See Also

Reference

OPENROWSET (Transact-SQL)
Hints (Transact-SQL)

Other Resources

Locking Hints
View Resolution

Help and Information

Getting SQL Server 2005 Assistance

Change History