Post reply

Just a random observation with 'WITH'

  • March 28, 2013 at 5:46 am

    #272274

    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).

  • March 28, 2013 at 6:23 am

    #1601586

    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

  • March 28, 2013 at 6:31 am

    #1601590

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply