Use of NOLOCK more | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Use of NOLOCK more

Hi, When I went through a SP I found this part; ================================================================================
IF NOT EXISTS (SELECT NULL FROM App WHERE AppID = @AppID)
BEGIN
SET @ValErr = 1
SET @ValString = ‘No’
GOTO ErrHandler_Validation
END SET @ApplicationID = (
SELECT AppID
FROM App (NOLOCK)
WHERE ApplicantID = @AppID
)
==================================================================================== I wondered why the developer used (NOLOCK) condition only in SELECT AppID
FROM App (NOLOCK)
WHERE ApplicantID = @AppID
and why not used in (SELECT NULL FROM App WHERE AppID = @AppID). I feel we can use the nolock condition in (SELECT NULL FROM App WHERE AppID = @AppID)
too. Can anyone please explain if there is a difference.
May be two reasons… 1. Developer want to read the committed data in IF NOT EXISTS statement to make sure he get the committed data… if the record exists then he wants to read the dirty read becasue he knows it is there… 2. Developers forgot to add the NOLOCK hint… If you use the following code you can skip additional call to the table when ApplicationID exists… SELECT @ApplicationID = AppID
FROM App a (NOLOCK)
WHERE ApplicantID = @AppID if @ApplicationID is null
BEGIN
SET @ValErr = 1
SET @ValString = ‘No’
GOTO ErrHandler_Validation
END
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I would have to agree with MohammedU’s second reasoning based on the queries you have posted supposedly from <b>your developer</b> so far on this NOLOCK topic. OR the coder doesn’t seem to know what NOLOCK does. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Happy coding.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
See your other psothttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20623 discussion. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>