Post reply

diffrence between nolock with braces and without braces

  • March 3, 2011 at 11:51 am

    #246423

    Can someone explain me about the difference between nolock with braces and without braces

    nolock (nolock)

    Rajesh Kasturi

  • March 3, 2011 at 11:55 am

    #1294207

    NOthing, actualy 😛 two ways of writing it 🙂

  • March 3, 2011 at 11:58 am

    #1294208

    I agree with Coldcoffee; no significant difference.

    Also i think i read in the future versions directives like that in the future will require the "WITH (nolock)" format, instead of the current optional leaving out of the WITH keyword.

    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!

  • March 3, 2011 at 11:58 am

    #1294209

    Should be used as WITH (NOLOCK) (if you need to use it in the first place ?)

    I think the ( ) is supposed to be mandatory in versions > sql2000

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 3, 2011 at 12:29 pm

    #1294244

    Just FYI using NOLOCK in an UPDATE or DELETE statement has been marked as deprecated in SQL 11 (Denali).

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 3, 2011 at 12:32 pm

    #1294250

    opc.three (3/3/2011)

    Just FYI using NOLOCK in an UPDATE or DELETE statement has been marked as deprecated in SQL 11 (Denali).

    and this command still works with the missing WITH in 2005/2008; i don't have R2 yet:

    select (name) from sys.tables(nolock)

    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!

  • March 3, 2011 at 1:27 pm

    #1294303

    If you leave the parenthesis off, the NOLOCK becomes a table alias, not a hint.

    -- NOLOCK is not a hint here, it's an alias, and has no effect on the query:

    SELECT NOLOCK.object_id, NOLOCK.name

    FROM sys.tables NOLOCK

    WHERE NOLOCK.schema_id = 1

    Eddie Wuerch
    MCM: SQL

  • March 8, 2011 at 7:49 pm

    #1296269

    great, thanks a lot for discussion

    Rajesh Kasturi

  • September 30, 2013 at 9:38 am

    #1654615

    Actually, "no difference" isn't strictly true.

    If you need to do (NOLOCK) as a table hint because you have a high transaction website then getting this wrong and doing:

    SELECT * FROM tablename NOLOCK

    can cause you a massive concurrency issue as your table won't do the dirty read you're expecting it to.

    We've had a problem today where an external agent updated some data, their process stuck and locked a table, that table had data that fed our homepage. Lock, lock, lock, lock, site death.

  • September 30, 2013 at 9:46 am

    #1654621

    Please note: 2 year old thread.

    Also, NOLOCK is not a good thing to have everywhere, locks are for consistency, for correct data. I don't know many users that like the idea of their reports being wrong from time to time (which nolock WILL cause).

    If you have blocking problems, tune the queries, tune the indexes and/or consider one of the row-versioning based isolation levels that get you lock-less selects without the duplicate/missing rows that nolock causes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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