Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008


DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008

Author
Message
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18081
In read-commited isolation level, readers don't block readers.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47143 Visits: 44346
homebrew01 (1/10/2014)
GilaMonster (1/10/2014)
No, it's an unnecessary use. If the data is static during the day then there aren't going to be any X locks around to block the reports and so adding nolock is a waste of typing


So am I mis-remembering situations in which long running read-only queries have caused blocking of other read-only queries ?


Read queries take shared locks (unless someone's been messing with locking hints). Shared locks never block other shared locks.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5681 Visits: 7660
homebrew01 (1/10/2014)
We have some semi-static reporting databases that run long queries. Some of them use nolock to avoid one report blocking another. In this case, the data was refreshed over night from production, and is static during the day. There is not really a concern about users getting up to the minute accurate information. Does this qualify as an acceptable use ?


The only thing you're doing here is reducing locking lag/escalation requirements, but they can't block each other.

TABLOCK would be equivalent, and safer, in a read only environment. Single lock opened, tiny overhead.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7417
kbhanu15 (1/9/2014)
Hi All,

can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?
which one is the best practice to use ?

Thanks
Bhanu


I think everyone did a good job explaining why you would want to avoid using read-uncommitted (AKA NOLOCK). That said, if NOLOCK is required (and getting the correct results when you run a query is not required) then I recommend always using: WITH (NOLOCK). Some things break when you omit the WITH keyword when using NOLOCK.

For example: this query would fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 (NOLOCK)



This would not fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 WITH (NOLOCK)



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
Alan.B (1/13/2014)
kbhanu15 (1/9/2014)
Hi All,

can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?
which one is the best practice to use ?

Thanks
Bhanu


I think everyone did a good job explaining why you would want to avoid using read-uncommitted (AKA NOLOCK). That said, if NOLOCK is required (and getting the correct results when you run a query is not required) then I recommend always using: WITH (NOLOCK). Some things break when you omit the WITH keyword when using NOLOCK.

For example: this query would fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 (NOLOCK)



This would not fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 WITH (NOLOCK)



I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important. w00t

The reason omitting the keyword WITH when defining a query hint is because leaving it off has been deprecated. You should ALWAYS use proper syntax in any t-sql statement and omitting WITH for a query hint is not correct syntax.

http://technet.microsoft.com/en-us/library/ms143729.aspx

To see the line in reference just search for "Specifying table hints without using the WITH keyword."

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18081
Sean Lange (1/13/2014)


I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important. w00t

I'm sure he meant that WITH(NOLOCK) should always be used instead of NOLOCK only when the hint is mandatory and not when integrity of the results is not important.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7417
I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.w00t


To be clear (and to avoid being the guy who suggested someone use always use NOLOCK in any situation) I do want to point out that I included the caveat, "If NOLOCK is required..." I think my point would have been clearer if I said: If there is a requirement to use NOLOCK then use the always use WITH. That said, I can't think of a good reason to use NOLOCK and aver that a requirement that anyone must use NOLOCK is ridiculous. ;-)


Edit: Just Louis' response; that is what I meant... Thank you sir.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
Alan.B (1/13/2014)
I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.w00t


To be clear (and to avoid being the guy who suggested someone use always use NOLOCK in any situation) I do want to point out that I included the caveat, "If NOLOCK is required..." I think my point would have been clearer if I said: If there is a requirement to use NOLOCK then use the always use WITH. That said, I can't think of a good reason to use NOLOCK and aver that a requirement that anyone must use NOLOCK is ridiculous. ;-)


Edit: Just Louis' response; that is what I meant... Thank you sir.


Yes 10000% agreed. I must have misread your original post and/or was just being a pissy boy at that moment. My comment was unintentionally (and hopefully uncharacteristically) rude. My apologies if I offended you, I certainly did not mean any.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Hany Helmy
Hany Helmy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1108
GilaMonster (1/9/2014)
The first is deprecated, the second is not, both can result in incorrect results, duplicate rows and missing rows, so neither is the best to use. Best is to write the SQL properly without hints.

Hi Gila,

What if we applied READ_COMMITTED_SNAPSHOT ISOLATION LEVEL on the database level? would that be enough to return only committed & not dirty data?
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5814 Visits: 11393
I would suggest (if NOLOCK is a requirement) to use NOLOCK, not WITH (NOLOCK).

NOLOCK without brackets is not a hint, but a table name alias.
So, the query would be using appropriate transaction isolation and no dirty reads would be involved.
Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search