syntax for nolock in sql

Question

I have seen sql statements using nolock and with(nolock) e.g -

select * from table1 nolock where column1 > 10

AND

select * from table1 with(nolock) where column1 > 10

Which of the above statements is correct and why?

Answer 1

The first statement doesn't lock anything, whereas the second one does. When I tested this out just now on SQL Server 2005, in

select * from table1 nolock where column1 > 10 --INCORRECT

"nolock" became the alias, within that query, of table1.

select * from table1 with(nolock) where column1 > 10

performs the desired nolock functionality. Skeptical? In a separate window, run

BEGIN TRANSACTION
UPDATE tabl1
 set SomeColumn = 'x' + SomeColumn

to lock the table, and then try each locking statement in its own window. The first will hang, waiting for the lock to be released, and the second will run immediately (and show the "dirty data"). Don't forget to issue

ROLLBACK

when you're done.

Answer 2

The list of deprecated features is at Deprecated Database Engine Features in SQL Server 2008:

• Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement.
• Specifying table hints without using the WITH keyword.
• HOLDLOCK table hint without parenthesis
• Use of a space as a separator between table hints.
• The indirect application of table hints to an invocation of a multi-statement table-valued function (TVF) through a view.

They are all in the list of features that will be removed sometimes after the next release of SQL, meaning they'll likely be supported in the enxt release only under a lower database compatibility level.

That being said my 2c on the issue are as such:

• Both from table nolock and from table with(nolock) are wrong. If you need dirty reads, you should use appropiate transaction isolation levels: set transaction isolation level read uncommitted. This way the islation level used is explictily stated and controlled from one 'knob', as opposed to being spread out trough the source and subject to all the quirks of table hints (indirect application through views and TVFs etc).

• Dirty reads are an abonimation. What is needed, in 99.99% of the cases, is reduction of contention, not read uncommitted data. Contention is reduced by writing proper queries against a well designed schema and, if necessary, by deploying snapshot isolation. The best solution, that solves works almost always save a few extreme cases, is to enable read commited snapshot in the database and let the engine work its magic:

  ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Then remove ALL hints from the selects.

Answer 3

They are both technically correct, however not using the WITH keyword has been deprecated as of SQL 2005, so get used to using the WITH keyword - short answer, use the WITH keyword.

Answer 4

Use "WITH (NOLOCK)".

Answer 5

Both are syntactically correct.

NOLOCK will become the alias for table1.

WITH (NOLOCK) is often exploited as a magic way to speed up database reads, but I try to avoid using it whever possible.

The result set can contain rows that have not yet been committed, that are often later rolled back.

An error or Result set can be empty, be missing rows or display the same row multiple times.

This is because other transactions are moving data at the same time you're reading it.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

There are other side-effects too, which result in sacrificing the speed increase you were hoping to gain in the first place.

Now you know, never use it again.