with(nolock) or (nolock) - Is there a difference?

sql sql-server select sql-server-2008-r2 nolock
Rob picture Rob · Aug 24, 2012 · Viewed 58.2k times · Source

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?

Answer

Aaron Bertrand picture Aaron Bertrand · Aug 24, 2012

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Best way to do nested case statement logic in SQL Server

I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/…

Read More