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

nolock Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:47 AM
Points: 25, Visits: 108
Has nolock been depricated in SQL Server 2012, or does the optimizer ignore the no lock and still run?
Post #1453739
Posted Thursday, May 16, 2013 12:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,250, Visits: 12,086
gchappell (5/16/2013)
Has nolock been depricated in SQL Server 2012, or does the optimizer ignore the no lock and still run?


I don't know that it has been deprecated but you should avoid using it anyway. Unless you want unpredictable results due to missing and/or duplicate data which can generate impossible to reproduce bugs I would not use it. Isolation levels are a better choice since they eliminate lots of the bizarre behavior with the NOLOCK hint.

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 #1453742
Posted Thursday, May 16, 2013 12:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
gchappell (5/16/2013)
Has nolock been depricated in SQL Server 2012, or does the optimizer ignore the no lock and still run?


nolock is always ignored for insert/update delete statements, as honoring that request could potentially violate the ACID principals ;

You can still use it for SELECT statements in SQL2012 (even though it can result in bad data, see the post above)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1453747
Posted Thursday, May 16, 2013 10:38 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 403, Visits: 287
See link. http://technet.microsoft.com/en-us/library/ms187373(v=sql.110).aspx



Note
For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


Mike

How to Post Performance Problems

How to Post Best Practices
Post #1453841
Posted Friday, May 17, 2013 3:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
mt_dren (5/16/2013)
See link. http://technet.microsoft.com/en-us/library/ms187373(v=sql.110).aspx



Note
For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


I guess, it's applicable if you do INSERT ... SELECT ... FROM table when NOLOCK is used for "from" table.
I can hardly see how you can update the table/row which will not be locked...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1453898
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse