Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008 Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 3:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:15 AM
Points: 980, Visits: 285
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
Post #1529268
Posted Thursday, January 9, 2014 3:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:21 AM
Points: 1,315, Visits: 2,538
kbhanu15 (1/9/2014)
Hi All,

can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?

Thanks
Bhanu


There is no diference between NOLOCK & WITH (NOLOCK). They both execute the query in "Read Uncommited" Isolation level.


which one is the best practice to use ?


No one, as they both may provide the result set with dirty reads.



Sujeet Singh
Post #1529272
Posted Thursday, January 9, 2014 3:57 AM


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 @ 7:10 PM
Points: 42,986, Visits: 36,141
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.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1529275
Posted Thursday, January 9, 2014 4:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:15 AM
Points: 980, Visits: 285
Thanks for you support.

it is mandatory in my query ti use(NOLOCK or WITH(NOLOCK), so i can prefer to use WITH (NOLOCK).

it is your conclusion.

Post #1529277
Posted Thursday, January 9, 2014 4:25 AM


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 @ 7:10 PM
Points: 42,986, Visits: 36,141
Why is a hint that will sooner or later result in incorrect results 'mandatory'? Do the people you're writing that query for realise that from time to time their results will be incorrect?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1529282
Posted Thursday, January 9, 2014 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 13,427, Visits: 12,292
NOLOCK is not a good habit at all. In fact query hints in general are a last resort and should only be used when you REALLY understand what they are doing. If you are unsure, avoid them.

Before you claim that NOLOCK is mandatory for your query you need to fully understand what that hint does and the serious issues it can and will bring to your system. Here are 3 very excellent articles describing what happens when you use that hint.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/


_______________________________________________________________

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 #1529340
Posted Friday, January 10, 2014 9:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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 ?


Post #1529877
Posted Friday, January 10, 2014 10:19 AM


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 @ 7:10 PM
Points: 42,986, Visits: 36,141
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


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1529890
Posted Friday, January 10, 2014 10:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 415, Visits: 2,439
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 ?

I've been in the situation where nolock use was mandatory. I'm pretty sure that the current supported syntax is WITH (NOLOCK).

Make sure you realise what actually happens and the ramifications of using it. In particular, knowledge of isolation modes is highly recommended when programming databases, I like the wikipedia articles on it. Nolock looks to be equal to read uncommitted.

http://en.wikipedia.org/wiki/Isolation_(database_systems)
Post #1529903
Posted Friday, January 10, 2014 1:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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 ?



Post #1529968
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse