Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 663
    Comment on it

    Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

     

    @@IDENTITY

     

    Syntax : SELECT @@IDENTITY

     

    The keyword "@@IDENTITY" in current session is capable of returning newly inserted or last record id of any given table but it’s functionality is not limited to current scope. If any trigger or functions in current session inserts a record in any table than "SELECT @@IDENTITY" will return this latest inserted record id regardless of table.


    SCOPE_IDENTITY()

     

    Syntax : SELECT SCOPE_IDENTITY()

     

    The keyword "SCOPE_IDENTITY()" in current session is capable of returning newly inserted or last record id of any given table. The functionality of "SCOPE_IDENTITY()" is limited to current scope i.e id of only newly inserted record via stored procedure or query execution in current session / connection will be returned even if 
    there are other functions or triggers that run automatically. 

     

    IDENT_CURRENT

     

    Syntax : SELECT IDENT_CURRENT(table_name)

     

    The keyword "IDENT_CURRENT" is capable of returning newly inserted or last record id of specified table. It’s limitation is not to any session or scope but is limited to specified table.

     

    Below is an example to demonstrate "@@IDENTITY","SCOPE_IDENTITY()","IDENT_CURRENT" property.

     

    Create two tables named DEMO1 and DEMO2

     

    CREATE TABLE DEMO1 (Id INT IDENTITY)
    CREATE TABLE DEMO2 (Id INT IDENTITY(100,1))

     

    Create a trigger in the same session as the session in which above table were created. This trigger will be executed when data is inserted into DEMO1 table.

     

    CREATE TRIGGER TRIGINSERT ON DEMO1 FOR INSERT
    AS
    BEGIN
    INSERT DEMO2 DEFAULT VALUES
    END


    Initially when following query are executed,both query will return empty value.

     

    SELECT * FROM DEMO1
    SELECT * FROM DEMO2

     

     

    Now insert default values in DEMO1 table by running following query.

     

    INSERT DEMO1 DEFAULT VALUES

     

    After inserting values in DEMO1 table, values of @@identity, scope_identity() and ident_current(‘tablename’) can be checked. Execute following query in sql server.

     

    1) SELECT @@IDENTITY 

     

    The above query will return record id 100 as it was last value inserted by trigger i.e as soon as default values was inserted in DEMO1 table, trigger TRIGINSERT was fired which insert default values in DEMO2 table therefore in DEMO2 table last record id was inserted.  

     

    2) SELECT SCOPE_IDENTITY()

     

    This query will return record id 1 as that was the last record id inserted through the query "INSERT DEMO1 DEFAULT VALUES" in current session,it does not take into account the values inserted into DEMO2 by trigger fired when default values were inserted in DEMO1 table.

     

    3) SELECT IDENT_CURRENT('DEMO2')

     

    This query does not depend on current session,it only depends on specified table,thus it returns values of "DEMO2" table.

     

    Executing all three queries together gives following output :-

     

     

     


     

    SQL Server : Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: