Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008 Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 2:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 PM
Points: 7,701, Visits: 16,627
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
Post #1529976
Posted Friday, January 10, 2014 2:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 45,116, Visits: 43,217
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

Post #1529982
Posted Friday, January 10, 2014 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 23, 2015 3:34 PM
Points: 5,467, Visits: 7,660
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
Post #1529988
Posted Monday, January 13, 2014 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 2,003, Visits: 6,704
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
Post #1530373
Posted Monday, January 13, 2014 12:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 15,902, Visits: 16,353
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.

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)
Post #1530406
Posted Monday, January 13, 2014 12:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 PM
Points: 7,701, Visits: 16,627
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.

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
Post #1530425
Posted Monday, January 13, 2014 1:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 2,003, Visits: 6,704
I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.


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
Post #1530426
Posted Monday, January 13, 2014 2:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 15,902, Visits: 16,353
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.


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)
Post #1530465
Posted Sunday, November 1, 2015 3:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, May 15, 2016 12:12 AM
Points: 2,456, Visits: 1,107
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?
Post #1732659
Posted Wednesday, November 4, 2015 8:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 5,439, Visits: 10,447
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.
Post #1733905
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse