SQL Server Performance

Wrong use of @@identity?

Discussion in 'General Developer Questions' started by merrillaldrich, Aug 11, 2005.

  1. merrillaldrich New Member

    Hi All -<br /><br />I administer a third party vendor's system, and in their stored procs I found this pattern which I find strange. Before embarassing myself by incorrectly complaining that this is wrong SQL <img src='/community/emoticons/emotion-1.gif' alt=':)' /> I wanted to see if others know more than I do.<br /><br />Here's the strange pattern:<br /><br />insert into SomeTable (column) values (value)<br />set @var = ( select @@identity SomeTable )<br /><br />To my eyes, this is just wrong, about three ways, and ought to be <br /><br />insert into SomeTable (column) values (value)<br />set @var = scope_identity()<br /><br />Am I just missing something? Why would one do the first version (other than misunderstanding identity)? The first thing does work, provided there are no triggers performing inserts, but to me it implies that the author wrongly imagines @@identity to be associated with a specific table, which it isn't. Also, it seems they imagine that you need a select just to assign the value to the variable, which makes the "table name" into a column alias.<br /><br />Weird.
  2. Luis Martin Moderator

    In BOL there is a good example:

    SCOPE_IDENTITY
    Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

    Syntax
    SCOPE_IDENTITY( )

    Return Types
    sql_variant

    Remarks
    SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

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

    For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

    Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

    @@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

    SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

    See Examples for an illustration.

    Examples
    This example creates two tables, TZ and TY, and an INSERT trigger on TZ. When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a row in TY.

    USE tempdb
    GO
    CREATE TABLE TZ (
    Z_id int IDENTITY(1,1)PRIMARY KEY,
    Z_name varchar(20) NOT NULL)

    INSERT TZ
    VALUES ('Lisa')
    INSERT TZ
    VALUES ('Mike')
    INSERT TZ
    VALUES ('Carla')

    SELECT * FROM TZ

    --Result set: This is how table TZ looks
    Z_id Z_name
    -------------
    1 Lisa
    2 Mike
    3 Carla

    CREATE TABLE TY (
    Y_id int IDENTITY(100,5)PRIMARY KEY,
    Y_name varchar(20) NULL)

    INSERT TY (Y_name)
    VALUES ('boathouse')
    INSERT TY (Y_name)
    VALUES ('rocks')
    INSERT TY (Y_name)
    VALUES ('elevator')

    SELECT * FROM TY
    --Result set: This is how TY looks:
    Y_id Y_name
    ---------------
    100 boathouse
    105 rocks
    110 elevator

    /*Create the trigger that inserts a row in table TY
    when a row is inserted in table TZ*/
    CREATE TRIGGER Ztrig
    ON TZ
    FOR INSERT AS
    BEGIN
    INSERT TY VALUES ('')
    END

    /*FIRE the trigger and find out what identity values you get
    with the @@IDENTITY and SCOPE_IDENTITY functions*/
    INSERT TZ VALUES ('Rosalie')

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
    GO
    SELECT @@IDENTITY AS [@@IDENTITY]
    GO

    --Here is the result set.
    SCOPE_IDENTITY
    4
    /*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/

    @@IDENTITY
    115
    /*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. merrillaldrich New Member

    Yes, thanks, that was what I understood to be true. I was just wondering if there was some logic to the other method, that I was missing, before I criticize it's author <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  4. FrankKalis Moderator

    No, just go ahead, but do you criticism in a decent way. As there are three different functionalities that basically do the same, the author just might have been unaware of SCOPE_IDENTITY(). [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page