Table Hint

Specify a table scan, index, or locking method for the query optimizer. Normally the query optimizer will pick the best optimization method without hints being specified.

Syntax
table_hint:
[NOEXPAND] hint [,hint...]
hints:
FASTFIRSTROW
HOLDLOCK
INDEX ( index_val [ ,...n ] )
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
Key:
NOEXPAND Indexed views are not expanded to access the underlying tables when
the query optimizer processes the query.
Index() Index hints - name or ID of indexes to be used by the query optimizer
KEEPIDENTITY Identity values in the imported data are to be used for the identity column.
During INSERT...SELECT...FROM OPENROWSET(BULK...)
KEEPDEFAULTS Use the columns default value instead of inserting NULLs.
During INSERT...SELECT...FROM OPENROWSET(BULK...)
FASTFIRSTROW Equivalent to OPTION (FAST 1)
HOLDLOCK See SERIALIZABLE.
IGNORE_CONSTRAINTS
Constrains on the table are ignored by the bulk-import operation
During INSERT...SELECT...FROM OPENROWSET(BULK...)
IGNORE_TRIGGERS
Tiggers defined on the table are ignored during INSERT...SELECT...FROM OPENROWSET
NOLOCK See READUNCOMMITTED.
NOWAIT Return a message as soon as a lock is encountered on the table.
PAGLOCK Always lock entire pages rather than just a row, key or single table lock.
READ COMMITTED Read operations comply with the rules for the READ COMMITTED isolation level
READCOMMITTEDLOCK
Comply with the rules for the READ COMMITTED isolation level.
READPAST Don''t read rows that are locked by other transactions.
This avoids blocking transactions - READ COMMITTED or REPEATABLE READ isolation levels.
READUNCOMMITTED
Dirty reads are allowed. No shared locks are issued, read any row including
exclusive locks set by other transactions.
REPEATABLEREAD The same locking semantics as REPEATABLE READ isolation level.
ROWLOCK Rows are locked in preference to page or table locks
SERIALIZABLE Hold shared locks until a transaction is completed
TABLOCK Hold a shared lock until the end-of-statement.
TABLOCKX An exclusive lock is taken on the table.
UPDLOCK Update locks are to be taken and held until the transaction completes.
XLOCK Exclusive locks are to be taken and held until the transaction completes.

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: SELECT...FROM ... WITH (hint, hint, hint)

Example

UPDATE MyTableWITH (TABLOCK)SET mt_Price = 5600WHERE mt_ID = 1234

"If you can suffer without a hint of self-pity, without a hint of
self-preoccupation, then this develops an almost limitless capacity for
compassion for everyone everywhere" ~
John Griffin