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)

Examples

UPDATE MyTable
WITH (TABLOCK)
SET mt_Price = 5600
WHERE 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

Related commands

SELECT
INSERT
UPDATE
DELETE
SET TRANSACTION ISOLATION LEVEL

Equivalent Oracle command:

Oracle Hints