Post reply

SCOPE_IDENTITY vs. @@IDENTITY

  • January 30, 2006 at 8:32 am

    #152716

    Can anyone provide some description of how SCOPE_IDENTITY actually works and what if any performance penalty is has over @@IDENTITY?  I understand what it does, but can find no reference to explain how it works...

    Thanks.

     

  • January 30, 2006 at 8:43 am

    #617751

    Hello Robert,

    Go through Books Online -> Scope_Identity.

    It has a detailed explanation with an example of how Scope_Identity and @@Identity works.

    Thanks and have a nice day!!!


    Lucky

  • January 30, 2006 at 10:20 am

    #617771

    Actually, the Books Online -> Scope_Identity describes what it does, but says nothing at all about how it does it.

    I would surmise that each connection has TWO global last_identity values, one for in scope, and one for any scope, and that @@IDENTITY returns the "any scope" and SCOPE_IDENTITY returns the global for changes at the same scope as the original processing.  But I don't actually KNOW that is what is happening, or that SCOPE_IDENTITY has the same performance as @@IDENTITY.

    Anyone know for sure?

  • January 30, 2006 at 3:44 pm

    #617845

    Personally and from experience, I wouldn't worry about perfromance on this issue!

    The reason is that a couple of years ago I got bitten very badly by using @@IDENTITY. One of my Stored Procedures retrieved the Incorrect Identity value and produced some silly money totals for a customer.

    From that day onwards, I never used @@IDENTITY but rather SCOPE_IDENTITY()

    Kindest Regards,

  • January 30, 2006 at 8:12 pm

    #617869

    Yep... I agree with Trigger... don't ever use @@IDENTITY because if a trigger fires and that trigger inserts into another table, @@IDENTITY will not contain the value you think it does.  Always use SCOPE_IDENTITY() instead of @@IDENTITY.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 31, 2006 at 11:23 am

    #617983

    Think of SCOPE_IDENTITY() as local and @@IDENTITY as global.  SCOPE_IDENTITY() is local to your procedure, where @@IDENTITY is the last identity value generated in the processing stream.  So, if you have a stored procedure that inserts data into a table that has an identity column, the insert might fire a trigger.  The trigger might be inserting data into another table that uses an identity column.  SCOPE_IDENTITY() will have the identity from the insert your procedure did and @@IDENTITY will have the identity value from the trigger's insert.

  • January 31, 2006 at 11:35 am

    #617986

    This post was about HOW these work, not what they do.  Can anyone confirm that there is NO performance difference between SCOPE_IDENTITY and @@IDENTITY?

  • January 31, 2006 at 3:46 pm

    #618022

    I appreciate your concern about performance between the 2 functions however, In this situation data integrity is far more important than performance as per my original reply!

    Kindest Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

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