Difference Among @@IDENTITY, SCOPE_IDENTITY() And IDENT_CURRENT()

Introduction

In this article, I am explaining 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 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. It is limited to the current session but not limited to the current scope. So @@IDENTITY has global scope in the database to get the last identity value; that value is generated for any table.tt

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 also insert a row in the table SUBUNIT. Now I will get the @@IDENTITY value, and it will be the SUBUNIT table's auto-increment column's 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 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 I will also insert a row in the SUBUNIT table. Now I will get the SCOPE_IDENTITY() value; then, it will be the UNIT table's auto-increment column's last identity value instead of the SUBUNIT table's last identity value.

IDENT_CURRENT():

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

Syntax

IDENT_CURRENT('Table_Name');

Example

Step 1. 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)
  )

Step 2. 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')

Step 3. Insert a row and get @@IDENTITY and SCOPE_IDENTITY()

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

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]

Step 4. 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.

Conclusion

Both @@IDENTITY and SCOPE_IDENTITY will return the last identity value in the current session but are different in their scope, but IDENT_CURRENT() does not depend on the current session and scope.