Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Just a random observation with 'WITH' Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 5:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 277, Visits: 699
Following is my observation with 'WITH'

I executed the below query
SELECT * FROM Sales.SalesOrderDetail (NOLOCK)

Which run sucessfully

now i execute the below query
UPDATE Sales.SalesOrderDetail_BACKUP (ROWLOCK)
SET UNITPRICE=1
WHERE PRODUCTID=707

(I know the query does not make sense but just a trail)

It gives me the error as
Incorrect syntax near '('.

If i change it to
UPDATE Sales.SalesOrderDetail_BACKUP WITH(ROWLOCK)
SET UNITPRICE=1
WHERE PRODUCTID=707

It Works. Just out of curiosity i would like to know why is WITH not mandatory with (NOLOCK).
Post #1436410
Posted Thursday, March 28, 2013 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
I believe that MS have stated that all hints will require the WITH statement in future to make it consistent.

You shouldn't be using the WITH (NOLOCK) hint It can have some chaotic effects.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1436431
Posted Thursday, March 28, 2013 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
a nolock on an update is ignored anyway, so there's no advantage to adding it anyway.

only SELECT statements will honor NOLOCK, and then you get the usual possibilities about doubled/repeated data, missing data, and data that is returned but doesn't exist any more.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse