52

Everything is based on the assumption that with(nolock) is entirely appropriate for the situtation. There are already plenty of questions out there debating whether or not to use with(nolock).

I've looked around and haven't been able to find if there is an actual difference between using with(nolock):

select customer, zipcode from customers c with(nolock)

or just (nolock):

select customer, zipcode from customers c (nolock)

Is there a functional difference between the two? Stylistic?
Is one older than the other and has a chance of being deprecated?

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
Rob
  • 834
  • 1
  • 10
  • 15
  • 1
    see here: http://stackoverflow.com/questions/1723910/syntax-for-nolock-in-sql – paul Aug 24 '12 at 16:02
  • 2
    They are alias. When the hint is specified with another option, the hint must be specified with the WITH keyword: `FROM t WITH (TABLOCK, INDEX(myindex))` http://msdn.microsoft.com/en-us/library/ms187373.aspx – edze Aug 24 '12 at 16:08
  • 1
    The Nolock can also be called as READUNCOMMITTED and it only applied inSELECT statements. It specifies that no shared locks can be issued against the table, which preventsother transactions from modifying the data in table. take a look on this post http://www.sqlserverlogexplorer.com/difference-between-nolock-and-with-nolock/ – Jason Clark Feb 23 '16 at 13:41
  • Good article [Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL Server](https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/). – Michael Freidgeim Aug 14 '23 at 13:11

3 Answers3

64

There is no functional difference, but eventually the syntax without WITH will not work. This has been deprecated:

select customer, zipcode from customers c (nolock)

So you should be using this format:

select customer, zipcode from customers c with (nolock)

Not using the WITH keyword for table hints has been deprecated since at least SQL Server 2008. Search the following topic for the phrase Specifying table hints without using the WITH keyword.:

http://msdn.microsoft.com/en-us/library/ms143729%28SQL.100%29.aspx

(Discussions about whether you should be using nolock at all, of course, are separate. I've blogged about them here.)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    For reference on when to use `with(nolock)`: http://stackoverflow.com/questions/686724/sql-when-should-you-use-with-nolock?rq=1 – Rob May 15 '13 at 20:03
  • @AaronBertrand The MSDN link that you specified indicates that NOLOCK is deprecated only for UPDATE and DELETE statements. It does not seem to be deprecated for SELECT statements. – Dono Sep 28 '17 at 02:15
  • @Dono I never said `NOLOCK` was deprecated. I said `NOLOCK` without the `WITH` keyword. Note the difference between my two code samples. – Aaron Bertrand Sep 28 '17 at 13:27
  • Any idea if "*will not work*" means it should issue an error (which it currently doesn't), or just silently won't execute the NOLOCK operation? – OfirD Nov 27 '22 at 09:02
  • @OfirD I expect it will be a syntax error, but it’s 10 years later and it still works. If we’ve had over a decade to fix our code and stop writing deprecated syntax, but we keep doing it, and some future version melts our servers as a result, we’d deserve it. In other words, why does it matter _how_ it will break? Just stop doing it (or, you know, [using NOLOCK at all](https://sqlblog.org/nolock), though it’s equally valid for other table hints). – Aaron Bertrand Nov 27 '22 at 13:35
  • @AaronBertrand agree. But I'm actually curious about ad-hoc scripts, I'm too lazy to add the `WITH` every time, and wonder if the `NOLOCK` actually doesn't lock. – OfirD Nov 27 '22 at 14:59
  • @OfirD if you’re looking for someone to tell you it’s ok to be lazy, I’m not your person. There is no reason to keep this ancient deprecated syntax anywhere, ad hoc or otherwise. – Aaron Bertrand Nov 28 '22 at 01:56
8

Though we dont find difference between (nolock) and with(nolock) ... with (nolock) would not work in SQL Server 2000 version.

And I also noticed that when you try to pull data from linked servers, just ' (nolock) ' will not work whereas you should use ' with (nolock) '. 

-- this will not work 
select * from server1.DB1.dbo.table1 (nolock)

-- this will  work 
select * from server1.DB1.dbo.table1 with (nolock)
pb2q
  • 58,613
  • 19
  • 146
  • 147
Vsh
  • 81
  • 1
  • 1
  • 1
    Thanks for taking the time to post. This should be a comment to Aaron's post above but I can see you don't yet have the reputation to post comments. +1 to get you on your way. – Rob Dec 23 '13 at 17:34
7

It really depends on which version of SQL Server you're on.

Checking out the latest documentation for SQL Server 2012 table hints omitting WITH is a deprecated feature. So while from customers c (nolock) will probably work; you should really be using from customers c WITH (nolock)

Note that this is different than from customers nolock; where nolock would serve as the table alias.

Functionally; they appear to be the same.

Jim B
  • 8,344
  • 10
  • 49
  • 77