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.