Post reply

What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED

  • November 15, 2011 at 6:57 am

    #246849

    What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED

    please explain this with example. i'm newbie

    also why it cannot be specified for tables modified by insert, update, or delete operations???

  • November 15, 2011 at 7:11 am

    #1407859

    Start with never use it assuming you care about data quality and you'll be better off.

    There are very specific case uses but they are rare.

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

  • November 15, 2011 at 7:17 am

    #1407862

    ashuthinks (11/15/2011)

    also why it cannot be specified for tables modified by insert, update, or delete operations???

    Because data modifications have to take exclusive locks to protect data integrity. Read uncommitted (as its name implies) only affects read operations.

    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

  • November 15, 2011 at 9:46 am

    #1408003

    As mention above use with caution.

    It all depends on what your database is used for. The organisation I currently work for read performance is paramount and every attention gets paid durring the Design \ Development \Test ..... stages.

    However as a coding standard we always specify with NOLOCK hints for select statements as blocking and waits caused by locking is to be minimized at all cost. This is based on a business decision and suits the industry we operate in. As dirty reads are acceptable.

    Where as my last employer was a clinical IT supplier where the most upto date data is paramount so the use was banned.

    Again it depends on the situation but table hints do have there place.

    MCITP SQL 2005, MCSA SQL 2012

  • November 15, 2011 at 9:51 am

    #1408007

    What industry are you in?

  • November 15, 2011 at 10:20 am

    #1408046

    ashuthinks (11/15/2011)

    What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED

    please explain this with example. i'm newbie

    also why it cannot be specified for tables modified by insert, update, or delete operations???

    Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)

    http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352

  • November 17, 2011 at 5:52 am

    #1409119

    Would rather not say but falls into the general category of Online advertising space.

    MCITP SQL 2005, MCSA SQL 2012

  • November 17, 2011 at 6:14 am

    #1409134

    RTaylor2208 (11/15/2011)

    As mention above use with caution.

    It all depends on what your database is used for. The organisation I currently work for read performance is paramount and every attention gets paid durring the Design \ Development \Test ..... stages.

    However as a coding standard we always specify with NOLOCK hints for select statements as blocking and waits caused by locking is to be minimized at all cost. This is based on a business decision and suits the industry we operate in. As dirty reads are acceptable.

    Where as my last employer was a clinical IT supplier where the most upto date data is paramount so the use was banned.

    Again it depends on the situation but table hints do have there place.

    If read performance is paramount, and you want the speed benefits and lack of blocking from nolock, why not snapshot isolation? Read Committed Snapshot Isolation will make tempdb grow a bit (how much depends on a lot of factors, mainly frequency and size of updates and deletes), but gives all the speed benefits of nolock, without the dirty reads drawbacks. I've seen dirty reads combined with connection pooling cause database corruption.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 17, 2011 at 8:52 am

    #1409272

    GSquared, I hadn't really considered that as an option, but will have a look thanks for the info

    MCITP SQL 2005, MCSA SQL 2012

  • November 17, 2011 at 1:22 pm

    #1409497

    OP, please don't start multiple threads for the same discussion, it causes confusion and results in too much redundancy. Refer to http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx

    Edit: Sorry I was a bit loud earlier...

    Jared
    CE - Microsoft

  • November 17, 2011 at 2:39 pm

    #1409558

    And late! 😀

    Dev (11/15/2011)

    ashuthinks (11/15/2011)

    What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED

    please explain this with example. i'm newbie

    also why it cannot be specified for tables modified by insert, update, or delete operations???

    Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)

    http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352

  • November 17, 2011 at 2:52 pm

    #1409571

    Ninja's_RGR'us (11/17/2011)

    And late! 😀

    Dev (11/15/2011)

    ashuthinks (11/15/2011)

    What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED

    please explain this with example. i'm newbie

    also why it cannot be specified for tables modified by insert, update, or delete operations???

    Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)

    http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352

    Touche Ninja... Touche...

    Jared
    CE - Microsoft

Viewing 12 posts - 1 through 11 (of 11 total)

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