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






4.78/5 (6 votes)
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 TableSUBUNIT
. I have a seed value different for each table so I can identify what value was obtained from@@IDENTITY
andSCOPE_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 theSUBUNIT
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
andSCOPE_IDENTITY()
values for theinsert
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 theUNIT
table while theSCOPE_IDENTITY()
value was from theSUBUNIT
table andIDENT_CURRENT()
shows the value as a parameter that is passed as the table name.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.