Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 494
    Comment on it

    In most of our applications, We need last inserted identity value in SQL Server table. For getting the last inserted identity value we have multiple options:


    1. @@IDENTITY
    2. SCOPE_IDENTITY
    3. IDENT_CURRENT

    All these three return the last inserted identity value in table but there value is differ based on session and scope


    @@IDENTITY:-

                @@IDENTITY is a global variable which returns the identity in current session and across the scope(i.e. global scope.).@@IDENTITY is limited to the current session, it is not limited to the current scope. Suppose that we have Insert trigger on table which causes the identity to be created on another table then @@IDENTITY returns that identity record which is created by trigger not from the table which triggered the insert trigger.


    SCOPE_IDENTITY:-

                SCOPE_IDENTITY is a function which will return the Identity from current session but it will also limit it to your current scope as well.It will return the identity created by table not by the trigger on that table


    IDENT_CURRENT:-

                 IDENT_CURRENT is a function which returns the last inserted identity for specific table in any session and in any scope.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. In other words, it is not affected by any scope and any session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.



    Example:-         I have created the table1,table2 and insert trigger on table1 . Please refer Below


    CREATE TABLE Table1(id int IDENTITY);    
    CREATE TABLE Table2(id int IDENTITY(50,1));    
    GO
    CREATE TRIGGER Inserttrigger ON Table1 FOR INSERT    
    AS   
    BEGIN   
       INSERT Table2 DEFAULT VALUES    
    END;    
    GO
    


    SELECT id FROM table1;
    --id is empty.
    
    SELECT id FROM table2;
    --Id is empty. 
    
    INSERT table1 DEFAULT VALUES;
    SELECT @@IDENTITY;
    /*Returns the value 50. This was inserted by the trigger.*/
    
    SELECT SCOPE_IDENTITY();
    /* Returns the value 1. This was inserted by the
    INSERT statement two statements before this query.*/ 
    
    SELECT IDENT_CURRENT('table2');
    /* Returns value inserted into table2, that is in the trigger.*/
    
    SELECT IDENT_CURRENT('table1');
    /* Returns value inserted into table1. 
    This was the INSERT statement four statements before this query.*/ 
    


    Do the following in new session(In new window)


    SELECT @@IDENTITY;
    
    /* Returns NULL because there has been no INSERT action
    up to this point in this session.*/ 
    
    SELECT SCOPE_IDENTITY();
    
    /* Returns NULL because there has been no INSERT action
    up to this point in this scope in this session.*/
    
    SELECT IDENT_CURRENT('table2');
    /* Returns the last value inserted into table2.*/
    

 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: