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)
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
SET TRANSACTION ISOLATION LEVEL
Equivalent Oracle command: