Menu
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?
923
The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
What does the SQL Server NOLOCK hint do? The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.
Understanding NOLOCK Hint The benefits of querying data using the NOLOCK table hint is that it requires less memory and prevents deadlocks from occurring with any other queries that may be reading similar data.
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk.
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.)
62
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)
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.
26
I tried this for a 170000+ data row result, however I did not see any difference through the query execution plan. Both work in the same way.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
