sqlsql-serversql-server-2008nolock

with(nolock) , (nolock) , nolock differences?

I know with(nolock) and (nolock) are the same or almost the same. REF:with(nolock) or (nolock) - Is there a difference?

but how about nolock? You can use either of them in select and the only notable difference I can see is when using alias that you can write:

select * from table1 as mytable with(nolock)

or

select * from table1 as mytable (nolock)

but you can't write:

select * from table1 as mytable nolock

PS: I'm not discussing nolock is good or bad here :)

Solution

  • The difference is that you should be using the syntax WITH (NOLOCK) (or WITH (<any table hint>)). Why?

    1. Without WITH is deprecated. From Table Hints on MSDN:

      Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    2. from table1 nolock does not apply a hint at all - that's an alias. For example:

      SELECT nolock.name FROM sys.objects nolock ORDER BY nolock.name;

      Notice that I can use nolock as an alias. No hint is applied here.

    3. from table1 as mytable nolock is invalid syntax in modern versions of SQL Server.

      Msg 1018, Level 15, State 1, Line 12
      Incorrect syntax near 'nolock'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

    You should also consider using the session-level hint, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, because then you don't have the syntax issue above, and you also don't have 15 WITH (NOLOCK)s littering your query. These make it harder to replace with a different isolation level later (like RCSI, which is far more practical than READ UNCOMMITTED IMHO), whereas the single batch-level statement is a very easy one-liner to replace.

    Also, and this is for other readers more so than for the OP, please be absolutely sure you understand the risks of using NOLOCK, which include getting corrupted data in a single row that never existed: