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 8 (of 8 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy