Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WITH(NOLOCK)

Author  Topic 

jrobin747
Starting Member

48 Posts

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-12 : 13:18:32
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 13:21:52
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

38200 Posts

Posted - 2013-08-12 : 13:25:48
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
   

- Advertisement -