views:

1198

answers:

4

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?

+3  A: 

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.

chadhoc
ok so with(nolock) has pretty much become the new syntax as of sql 2005, although nolock by itself still works. is that it?
seenasan
yes, still works without the "with", but it has been deprecated, meaning it will not work that way most likely in a release at some point in the future (could be the next release, could be the one after that, could be 10 from now)...
chadhoc
also about insert, update should with(nolock) be used there too as a programming norm?
seenasan
I wouldn't say using table hints in any manner would/should be considered a programming norm at all - there are scenarios where it can/should be used, but definitely not a "norm". If you're asking CAN you use them in insert/update statement, you certainly can use them within the FROM clauses of the insert/update, but you can't nolock a write (i.e. the actual insert/update).
chadhoc
Inserts and Updates (and Deletes) will ignore nolock -- at least in SQL Server.
Philip Kelley
The write portion will, yes - hence the statement that you can't nolock a write but you can use them in the FROM portion of the clause (i.e. insert tableA (a,b,c) select d,e,f from tableB with(nolock)) - the tableB seek/scan will honor the nolock, the tableA portion will not.
chadhoc
When I said "The write portion will, yes" I was agreeing with you Philip that the actual insert/update/delete will ignore the nolock...confusing after I read it
chadhoc
I get it--kind of subtle. However, I think it'd be a *very* bad practice to combine data modifications and nolocks this way.
Philip Kelley
I would agree for the majority of cases, absolutely
chadhoc
+1  A: 

Use "WITH (NOLOCK)".

Rob Garrison
+4  A: 

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

"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.

Philip Kelley
+1 For catching the distinction of (nolock) vs. nolock - spot on in regards to the alias stuff. The distinction should be the use of "(nolock) vs. with(nolock)" rather than "nolock vs. with(nolock)" as is shown in the question - good catch
chadhoc
+5  A: 

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 uncommited. 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 uncommited 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.

Remus Rusanu
+1 for a couple of things, first time I noticed the deprecation of nolock in the FROM for update/deletes - very nice. Though I don't agree totally that dirty reads are an abomination (there are some scenarios where it can actually make sense to use, even over a read committed optimistic approach), I do agree that it is overused, misunderstood, and there are very, very few scenarios where it makes sense and even fewer where the use is actually understood.
chadhoc
I agree that dirty reads have their use, but lets say I like to use dramatic effect to emphasize the message.
Remus Rusanu