SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WITH(NOLOCK)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 08/12/2013 :  12:57:48  Show Profile  Reply with Quote
What is the purpose of using WITH(NOLOCK)?
My co worker told me to add it to my FROM

tkizer
Almighty SQL Goddess

USA
36681 Posts

Posted - 08/12/2013 :  13:18:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
The purpose is to get bad data.

Your co-worker is misinformed. People think it's the turbo boost button, but it's not. It's the "you're crazy" button. Dirty reads are possible and are eventually going to happen. This option should really only being used ad-hoc and when the returned data is okay to be wrong or a tad off.

Here's what the documentation says about NOLOCK (check the READUNCOMMITTED section as they are equivalent): http://technet.microsoft.com/en-us/library/ms187373.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 08/12/2013 :  13:21:52  Show Profile  Reply with Quote
quote:
Originally posted by jrobin747

What is the purpose of using WITH(NOLOCK)?
My co worker told me to add it to my FROM

No lock query hint allows you to read dirty data from the database.

By default SQL Server uses READ COMMITTED isolation. What that means is that the data that you read will be committed data. You would not read data that has been only partially committed, and hence possibly inconsistent and incorrect.

Sometimes people use NOLOCK hints on readonly databases. The rationale being that there is no data modification going on, then there can be no dirty pages.

Sometimes people use NOLOCK even on read/write databases under the notion that it cures all performance problems that they may encounter now or in the future. I wouldn't recommend it unless you don't really care about the correctness of your data.

You can ask your co worker what his/her rationale for suggesting it is.

Google for isolation levels and you will find lot of useful information. Here is one for example: http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/08/19/isolation-levels-in-sql-server/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36681 Posts

Posted - 08/12/2013 :  13:25:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
Ah yes and for read-only databases! I wouldn't bother using it there though, but yes that's a legitimate place to use it.

We use read committed snapshot isolation level (RCSI) for our OLTP databases. Unless you are using read committed and not overriding it with hints, be sure you understand the different isolation levels and how they can impact your application and the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000