WITH (

)

Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. For more information, see Table Hint (Transact-SQL).

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 by using parentheses.

The table hints allowed with and without the WITH keyword are the following: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints must be specified alone. For example: FROM t (fastfirstrow).

When the hint is specified with another option, as in (fastfirstrow, index(myindex), the hint must be specified with the WITH keyword, such as:

FROM t WITH (fastfirstrow, index(myindex)).

The WITH keyword is not mandatory with hints when the database has a compatibility level of 80 or earlier.