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

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 that are 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:

Syntax: IDENT_CURRENT('Table_Name');

EXAMLPE

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)
  )

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')

3. 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]

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 the return last identity value in the current session but are different in their scope but IDENT_CURRENT() does not depends on current session and current scope..