Table Hint Descriptions
Now that you know the names of the hints, how they are grouped, and the syntax of each, lets go over what each hint does.
INDEX is used to specify the name or object ID of an index or indexes that will be used by SQL Server when processing the statement. SQL Server will chose an index by default if one is not specified by the use of the INDEX keyword, but sometimes you will need to force SQL Server to use a particular index.
Only one index hint can be used per table, but you can specify more than one index in this hint. If a clustered index exists on the specified table, using INDEX(0) will force a clustered index scan and INDEX(1) will force a clustered index scan or seek. If no clustered index exists on the table, then INDEX(0) will force a table scan while INDEX(1) will be interpreted as an error.
If you chose multiple indexes to be used for the hint, any duplicates will be ignored. Be careful in the order you place indexes when you use multiple indexes with the index hint. SQL Server try to apply as many conditions as possible on each index, so if you place the broader indexes first, you may have all your conditions covered and SQL Server will not have to create AND statements for all the specified indexes.
You can use a maximum of 250 non-clustered indexes in an index hint. Be aware that if an index hint with multiple specified indexes is used on a fact table in a star join, then SQL Server will ignore will return a warning message and ignore the hint.
FASTFIRSTROW will optimize the query to retrieve the first row of the result set.
HOLDLOCK (equivalent to SERIALIZABLE) applies only to the table specified and only for the duration of the transaction, and will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option specified.
NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.
PAGLOCK will force the use of a page lock instead of a table lock.
READCOMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, which will result in nonrepeatable reads and may cause phantom data. READCOMMITTED is the default table hint in SQL Server.
READPAST specifies that locked rows be skipped during the read. READPAST only applies to transactions operating at the default READ COMMITTED isolation level, and will only read past row-level locks. READPAST can only be used in SELECT statements. Normal blocking can be worked around by having transactions read past rows being locked by other transactions.
READUNCOMMITTED (equivalent to NOLOCK) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.
REPEATABLEREAD specifies that locks be placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.
ROWLOCK forces the use of row-level locks instead of page or table level locks.
SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.
TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be held until the end of the statement.
TABLOCKX specifies that an exclusive lock be held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.
UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.
XLOCK specifies that an exclusive lock be used and held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.