Click here to Skip to main content
15,867,308 members
Articles / Web Development / HTML
Tip/Trick

Interview Question: What are the Differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

Rate me:
Please Sign up or sign in to vote.
4.78/5 (6 votes)
14 Jul 2014CPOL3 min read 18.9K   11   5
How to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT())

Introduction

In this tip, I explain how to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT()). Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value that is produced in a single session; in other words a connection while IDENT_CURRENT() returns the last identity value according to the table for any session.

@@IDENTITY

It returns the last identity value generated for a table in the current session. This table can be any table in the database. So @@IDENTITY has global scope in the database to get the last identity value; that value is generated for any table. It is limited for the current session but not limited for the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the table UNIT that inserts a row in the table SUBUNIT. Now I insert a row in the table UNIT so my trigger will be fired and will insert a row in the table SUBUNIT also. Now I will get the @@IDENTITY value and it will be the SUBUNIT table's auto increment column last identity value instead of the UNIT table's last identity value.

SCOPE_IDENTITY()

It returns the last identity value generated for a particular table in the current session. It is not only limited to the current session but also limited to the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the UNIT table that inserts a row in the UNIT table. Now I insert a row in the UNIT table so my trigger will be fired and will insert a row in the SUBUNIT table also. Now I will get the SCOPE_IDENTITY() value, then it will be the UNIT table's auto increment column last identity value instead of the SUBUNIT table's last identity value.

IDENT_CURRENT()

It returns the last identity value generated for any table that is passed as a parameter for it. It is neither limited to the current session nor to the current scope. But it is limited to a table, in other words, it depends on the table that is passed as the parameter. See:

SQL
IDENT_CURRENT('Table_Name');

Using the Code

  • Create Two Tables

    I create two tables Table UNIT and Table SUBUNIT. I have a seed value different for each table so I can identify what value was obtained from @@IDENTITY and SCOPE_IDENTITY() and for which table.

    SQL
    CREATE TABLE UNIT
    (
            Id INT IDENTITY(1,1),
            Name NVARCHAR(50)
    )
     
    CREATE TABLE SUBUNIT
        (
            Id INT IDENTITY(10,1),
            Name NVARCHAR(50)
      )
  • Create Trigger

    I create a trigger on Table UNIT that inserts a row in the SUBUNIT table.

    SQL
    CREATE TRIGGER I_UNIT
    ON UNIT
    FOR INSERT
    AS
    INSERT INTO SUBUNIT(Name)
    VALUES ('gm')
  • Insert a row and get @@IDENTITY and SCOPE_IDENTITY()

    I insert a row in the UNIT table and get both of the @@IDENTITY and SCOPE_IDENTITY() values for the insert statement. See:

    SQL
    INSERT INTO UNIT(Name)
    VALUES ('Kg')
     
    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
    SELECT  IDENT_CURRENT('UNIT') AS [IDENT UNIT]
     
    SELECT @@IDENTITY AS [@@IDENTITY]
    SELECT  IDENT_CURRENT('SUBUNIT')AS [IDENT SUBUNIT]
  • OUTPUT

    The following figure shows that we got the @@IDENTITY value from the UNIT table while the SCOPE_IDENTITY() value was from the SUBUNIT table and IDENT_CURRENT() shows the value as a parameter that is passed as the table name.

    Output of query

    Figure 1.1 Output of query

Conclusion

Both @@IDENTITY and SCOPE_IDENTITY will the return last identity value in the current session but are different in their scope, but IDENT_CURRENT() does not depend on current session and current scope. I hope it will be helpful for you and if you have any doubt or feedback, then post your comments here or you can directly connect with me at https://twitter.com/ss_shekhawat.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
He is awarded for Microsoft TechNet Guru, CodeProject MVP and C# Corner MVP. http://l-knowtech.com/

Comments and Discussions

 
GeneralMy vote of 5 Pin
Lydia Gabriella16-Jul-14 10:43
Lydia Gabriella16-Jul-14 10:43 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat16-Jul-14 16:20
professionalSandeep Singh Shekhawat16-Jul-14 16:20 
Generalneed to be aware of this Pin
Brian A Stephens14-Jul-14 2:55
professionalBrian A Stephens14-Jul-14 2:55 
Thanks for the tip. It's one that coders need to be aware of, or they will get tripped up by "wrong" values returned by the calls. I have been caught by it before.

There's a typo in the SCOPE_IDENTITY section that's important to fix: you said the trigger will insert into the UNIT table, instead of SUBUNIT.
GeneralRe: need to be aware of this Pin
Patrick Harris16-Jul-14 4:19
Patrick Harris16-Jul-14 4:19 
GeneralRe: need to be aware of this Pin
Sandeep Singh Shekhawat16-Jul-14 5:13
professionalSandeep Singh Shekhawat16-Jul-14 5:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.