65.9K
CodeProject is changing. Read more.
Home

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

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.78/5 (6 votes)

Jul 14, 2014

CPOL

3 min read

viewsIcon

19873

downloadIcon

35

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:

 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.

    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.

    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:

    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.