As you advance in your skills as a Transact-SQL developer or SQL Server database administrator there will come a time

when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL

provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements

to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation level.

This article will describe the use of the table-level locking hints and general table hints that are available to

Transact-SQL developers and SQL Server database administrator and attempt to provide scenarios on when you should use a

particular hint. To steal from Microsoft's documentation you should note that SQL Server's query optimizer will

automatically determine what it thinks is the best type of lock to use on an object in a query and you should only override

this choice only when necessary. After saying that, it is sometimes necessary to correct the optimizer and use a hint to

force the optimizer to use an index other than the one it picks or to simply control the behavior of the locks. An example

of choosing an alternative index would be when the query optimizer chooses an index that is constantly out of date and

you do not have the cycles to bring the index up to date before you run the query, while an example of controlling the

locking behavior would be to lock a table to optimize a BULK INSERT.

Table Hint May Not Be Used By Query Analyzer

Developers should note that even though you specify a table-level hint in your code, the query optimizer may ignore the hint.

Table-level hints will be ignored if the table is not chosen by the query optimizer and used in the subsequent query plan.

The query analyzer will often choose an indexed view over a table and in case your table-level hint will be ignored, you

can override the query analyzer's preference for indexed views by using the OPTION (EXPAND VIEWS) query hint. Another

reason the query analyzer may ignore your hint is due to the fact that the table may contain computed columns and the

computed columns are computed by expressions and functions referencing columns in other tables and the table hints are not

specified for those tables. Table hints are not propagated on tables with computed columns so the hint will not be used on

tables referenced by computed columns, table-level hints are propagated on base tables and views referenced by another view

though. SQL Server also does not allow more than one table hint from either the Granularity hint group (PAGLOCK, NOLOCK,

ROWLOCK, TABLOCK, and TABLOCKX) or the Isolation Level hint group (HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, and

SERIALIZABLE) to be used in the FROM clause for each table. This basically means that you cannot specify ROWLOCK and

TABLOCK for a table in the same FROM clause. SQL Server will also not allow the NOLOCK, READUNCOMMITTED, or READPAST hints

to be used against tables that are the targets of a DELETE, INSERT or UPDATE statement.

Table Hint Syntax

Now that we have mentioned most of the hints lets look at the syntax for table-level hints as used with the FROM clause.

SYNTAX
[ FROM { < table_source > } [ ,...n ] ] 
< table_source > ::= 
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] 
    
< table_hint > ::= 
    { INDEX ( index_val [ ,...n ] ) 
        | FASTFIRSTROW 
        | HOLDLOCK 
        | NOLOCK 
        | PAGLOCK 
        | READCOMMITTED 
        | READPAST 
        | READUNCOMMITTED 
        | REPEATABLEREAD 
        | ROWLOCK 
        | SERIALIZABLE 
        | TABLOCK 
        | TABLOCKX 
        | UPDLOCK 
        | XLOCK 
    } 

Microsoft encourages the use of the WITH keyword even though it is optional as they state that a future release of SQL

Server may require this keyword. Microsoft also encourages the use of the optional commas between different hints as

spaces are only allowed for backward compatibility reasons. You can see that there are fifteen hints defined in this

syntax listing. Thirteen of those hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED,

REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level locking hints

while (INDEX and FASTFIRSTROW) are considered table hints. Doesn't mean much to most developers, but I thought I would

tell you how Microsoft groups them.

Table Hint Descriptions

Now that you know the names of the hints, how there 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) will apply 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) will 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 will

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 will specify 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) will 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 will specify 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 will force the use

of row-level locks instead of page and table level locks. SERIALIZABLE (equivalent to HOLDLOCK) will apply 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. TABLOCK will for 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 will use update locks instead of shared locks and will hold the locks until the end

of the statement or transaction. XLOCK will use 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 hint.

Table Hint Usage

Now that you know what the definition is for each of the table-level hints you may be wondering when you may need to use

them. At Verizon we have a very large databases that are used by approximately 10,000 customer service reps in a call

center environment. While the reps are using the database we have to load approximately 400,000 new rows of data into the

database every three days. This load process can take up to 16 hours so we are often forced to run the load during

operational hours. To optimize our load process we have made the BULK INSERT process use the TABLOCK hint to lock tables

and speed their inserts and in order to do this we use the READUNCOMMITTED hint (we could as easily used the NOLOCK hint)

to allow dirty reads of the data, all transactions generated by the reps are placed into smaller transactional databases

so dirty reads are not a problem. Our very large databases and large number of monthly inserts created a slight problem

with the table statistics and indexes being out of date, not enough operating cycles to keep all statistics completely

updated at all times and rebuild indexes after every data load. This problem will cause the query analyzer to try to create

query plans against these outdated indexes or statistics and sometimes cause the query to perform in a sub-optimal manner.

This forces me to use the index hint to force a query to use an index or indexes that I know is keep updated and solve the

slow response time. Another use of the index hint is to force the use of an index when the query optimizer insists on

using a table scan. The SQL Server 7.0 query optimizer seems to have a preference for table scans even though very few

rows of the table will be returned by the query.

I don't tend to use the other hints at Verizon, but in the past I have found use for the READPAST hint in work queues to

allow a row of data to be returned to a client without waiting for locks to be releases by other processes, nice to use if

you do not want end users to obtain the same row of data. Others have told me that they often use the FASTFIRSTROW hint

when they want to return the first row to the user, to give them something to do, while the rest of the query catches up.

Haven't had too many uses for TABLOCKX, UPDLOCK, XLOCK, or SERIALIZE (HOLDLOCK) in the type of databases I'm accustomed to

working with but have been told that they are great hints for financial and reporting situations when you need the data to

be consistent through out a transaction. Different hints are needed for different types of databases or transactions and

you will eventually determine which ones are proper for your situation.

Summary

As you can see table-level hints are available for use by Transact-SQL developers or SQL Server database administrators,

but should only be used to fine-tune your code and not as a standard for certain types of queries. You should perform a

strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want

you need to solve your problem, also perform this strict review after the hint is in place. While table-level hints are

not for all levels of users, experienced administrators and developers can use them to solve a limited set of problems as

well as fine-tune a limited set of queries in which the query optimizer has failed in its job to optimize correctly.

Knowledge Based Articles

Q235880 INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605

Q297466 BUG: READPAST Locking Hint Returns an Incorrect Number of Rows

Q308760 FIX: SQL Server Optimizer Ignores Index Hint for UPDATE If One or More Non-Clustered Indexes Exist

Q308886 PRB: NOLOCK Optimizer Hint May Cause Transient Corruption Errors in the SQL Server Error Log

Q310935 FIX: Use of a Dynamic API Server Cursor with a NOLOCK Hint Causes Error 1047

Q320434 FIX: Bulk Insert with TABLOCK Hint May Result in Errors 8929 and 8965 When You Run CHECKDB

Q247365 BUG: Dynamic Cursor With NOLOCK Hint, DELETE Activity, Causes Assertions in Error Log

Copyright 2002 by Randy Dyess, All rights Reserved