Home / Questions / How to get the last generated identity column value in SQL Server
Explanatory Question

How to get the last generated identity column value in SQL Server

👁 569 Views
📘 Detailed Answer
🕒 Easy to Read
Read the answer carefully and go through the related questions on the right side to improve your understanding of this topic.

Answer with Explanation

Example queries for getting the last generated identity value


Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblPerson')

Let's now understand the difference between, these 3 approaches.

SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same Stored procedure, function, trigger). Let's say, I have 2 tables tblPerson1 and tblPerson2, and I have a trigger on tblPerson1 table, which will insert a record into tblPerson2 table. Now, when you insert a record into tblPerson1 table, SCOPE_IDENTITY() returns the idetentity value that is generated in tblPerson1 table, where as @@IDENTITY returns, the value that is generated in tblPerson2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope. IDENT_CURRENT('tblPerson') returns the last identity value created for a specific table across any session and any scope.

In brief:
SCOPE_IDENTITY() - returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('TableName') - returns the last identity value that is created for a specific table across any session and any scope.