0

When I prefer to use WITH (NOLOCK) in all the SQL queries inside a specific large stored procedure, is there a generic way to use it for all the specific stored procedure statements, or I should use WITH (NOLOCK) for every individual query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Omtechguy
  • 3,321
  • 7
  • 37
  • 71
  • 2
    I might be wrong, but this sounds like premature optimization. – Lucero Apr 08 '12 at 12:12
  • 5
    This not not just an optimization, it is likely to make the application incorrect. – usr Apr 08 '12 at 13:04
  • @usr, I agree, but I was really referring to the motivation to make such changes in the first place, not so much about this specific one. – Lucero Apr 08 '12 at 14:11

2 Answers2

9

You could set the Transaction Isolation Level

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

However, don't forget that NOLOCK means your queries can potentially return dirty or duplicated data, or miss out data altogether. If it's an option for you, I would suggest investigating the READ_COMMITTED_SNAPSHOT database option to allow you to avoid locking issues while returning queries with consistent results.

MartW
  • 12,348
  • 3
  • 44
  • 68
4

You want to use the following syntax:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I found this by looking at the NOLOCK table hint located here : http://msdn.microsoft.com/en-us/library/ms187373.aspx. The WITH(NOLOCK) table hint is equivalent to setting the isolation level to be READ UNCOMMITTED. Here's the snippet from MSDN (http://msdn.microsoft.com/en-us/library/ms187373.aspx):

NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

Mez
  • 4,666
  • 4
  • 29
  • 57