nolock

Question

nolock

gchappell

SSC Eights!

Points: 845
More actions
May 16, 2013 at 12:45 pm

#274021

Has nolock been depricated in SQL Server 2012, or does the optimizer ignore the no lock and still run?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Montana Life Right there with Babe Points: 720 More actions · Answer 3

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[/url]

How to Post Best Practices[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]