articles >> performance tuning >> SQL Server 2000 Table Hints

SQL Server 2000 Table Hints

By : Randy Dyess Jan 01, 2004

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 levels.

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 administrators, and attempt to provide scenarios on when you should use a particular hint. 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. An example of controlling the locking behavior would be to lock a table to optimize a BULK INSERT.

 

Table Hints May Not Always Be Used By the Query Optimizer

You 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.

In addition, the query optimizer will often choose an indexed view over a table. In case your table-level hint will be ignored, you can override the query optimizer'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, TABLOCKX), or the Isolation Level hint group (HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, 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, let's look at the syntax for table-level hints 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 these 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. This doesn't mean much to most developers, but I thought I would tell you how Microsoft groups them.