SQL Server Performance

@@IDENTITY Vs SCOPE_IDENTITY

Discussion in 'T-SQL Performance Tuning for Developers' started by Madhivanan, Sep 20, 2005.

  1. Madhivanan Moderator

    From BOL,

    @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

    But I see that both lose their scope in new instance
    What is the significant difference?

    Madhivanan

    Failing to plan is Planning to fail
  2. Adriaan New Member

    You haven't read the rest of the information under SCOPE_IDENTITY, have you?

    The explanation is that @@IDENTITY doesn't limit itself to the current code that is executing. If your code inserts a row on a table T1 (with an identity column) and T1 has an insert trigger that inserts a row in table T2 (with an identity column) then @@IDENTITY returns the identity value for T2 (occurred in the trigger, so outside of the scope) and SCOPE_IDENTITY returns the identity value for T1 (occurred in the procedure, so within the scope). But if there is no trigger on T1, then both @@IDENTITY and SCOPE_IDENTITY will return the same identity value.

    You can always use IDENT_CURRENT('table_name') to get the most recently inserted identity value for a specific table - but you have to remember that this is in no way related to the current scope, and it will return a value even if no row was inserted by your code.

    You'll have to wrap the whole thing in a transaction to get any dependable results. Also it can only give you the last inserted value, so it won't cover multiple rows ...
  3. FrankKalis Moderator

    I think the most detailed explanations and the example illustrating it all one can find in BOL under IDENT_CURRENT. Looks like the wrong place, because most folks onyl know about @@IDENTITY and/or SCOPE_IDENTITY().

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. Madhivanan Moderator

    Well. I see under @@Identity but IDENT_CURRENT has full details. Thanks for that point

    Madhivanan

    Failing to plan is Planning to fail

Share This Page