with(nolock) , (nolock) , nolock differences?
nolock, sql, sql-server, sql-server-2008
Solution
The difference is that you should be using the syntax `WITH (NOLOCK)` (or `WITH (<any table hint>)`). Why?
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.
`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.
`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:
- Bad habits : Putting NOLOCK everywhere
- The Read Uncommitted Isolation Level (Paul White)
Problem
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 :)