NoLock Rookie Question - SQL Server Forums

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

NoLock Rookie Question

Author

Topic

sammuts
Starting Member

3 Posts

Posted - 2010-12-04 : 04:11:51

Hi,

I have the following code which updates a field InvoiceNo with a numeric variable according to a select statement

Code:
UPDATE CR_CONSIGNMENT_CHARGES
SET InvoiceNo = @iInvoiceNo
FROM CR_CONSIGNMENT_CHARGES A WITH (NoLock)
JOIN CR_CONSIGNMENTS B ON B.ConsignmentID = A.ConsignmentID
JOIN CR_CLIENTS C ON C.Code = CASE ISNULL(A.ChargeAccount,'')
WHEN '' THEN B.ClientChargeCode
ELSE A.ChargeAccount
END
WHERE A.ConsignmentID = @iConsignmentID
AND C.Code = @sChargeAccount
AND (ISNULL(@sChargeCode,'') = ''
OR (ISNULL(@sChargeCode,'') <> ''
AND A.ChargeCode = @sChargeCode))
AND ISNULL(A.InvoiceNo,0) = 0

As you can see in the select statement I am using the NoLock just in case someone else already updated the field A.InvoiceNo which is something that might happen due to the amount of user running my system. Is this a good practice or there is a better way I can achieve this?

10x
Ivan

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 04:55:55

Why dont you put it in Transaction ?

Vaibhav T

If I cant go back, I want to go fast...

sammuts
Starting Member

3 Posts

Posted - 2010-12-04 : 05:40:01

They are in a start transaction

Kristen
Test

22859 Posts

Posted - 2010-12-04 : 08:36:24

"Is this a good practice or there is a better way I can achieve this?"

NOLOCK is usually a bad thing. Do you understand the potential issues you will face? Dirty data, missing data, data duplicated in the resultset etc?

What is the problem you are trying to solve? (rather than your suggested solution

)

I don't know quite how SQL will interpret your UPDATE phrase (this has come up here before, and I think the parser is "loose" on this!), but:


UPDATE CR_CONSIGNMENT_CHARGES A
SET InvoiceNo = @iInvoiceNo 
FROM CR_CONSIGNMENT_CHARGES AS A

would be unambiguous.


AND (
    ISNULL(@sChargeCode,'') = '' 
    OR (
        ISNULL(@sChargeCode,'') <> '' 
        AND A.ChargeCode = @sChargeCode
    )
)

could be replaced by


(ISNULL(@sChargeCode,'') = '' OR A.ChargeCode = @sChargeCode)

I don't know if it makes any difference to the Query Optimiser, but its a bit easier to read.


JOIN CR_CLIENTS C 
    ON C.Code = CASE ISNULL(A.ChargeAccount,'')
                    WHEN '' THEN B.ClientChargeCode
                    ELSE A.ChargeAccount
                    END

can be replaced (I think!) with


JOIN CR_CLIENTS C 
    ON C.Code = ISNULL(A.ChargeAccount, B.ClientChargeCode)

but it is still not very efficient, although probably more efficient than a CASE statement.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:51:08

Very nice explaination Kristen

Vaibhav T

If I cant go back, I want to go fast...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-04 : 12:35:37

Using NOLOCK during an UPDATE FROM is always a bad idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources