Difference Between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT

Introduction

In this article i will explain about what is Scope_Identity(), @@Identity and Ident_Current. Difference between these three with example.

Why Do We Need These

All these three function Scope_Identity(), @@Identity and Ident_Current are use to returning last inserted index value from table. 

@@IDENTITY()

It returns last inserted identity value for any table in current connection or session and any scope.If you have trigger on your table then @@IDENTITY returns last inserted record Id by Trigger not by table.

For example : If you are performing any DML operation in your table named A and table A having a insert trigger  which is inserting row in another table named B then @@IDENTITY returns last inserted record Id  from B table not from A table.

SELECT @@IDENTITY

Scope_Identity()

It returns the last inserted index or identity value for any table in current connection and current scope. Scope_Identity returns different values in different connection or session just opposite of  @@Identity. If you have trigger on your table then @@IDENTITY returns last inserted record Id  from table.

For example : If you are having a insert trigger on your table A and it will inserting record into another table B than Scope_Identity returns last inserted Identity value from table A. 
But If trigger is performing insert operation in the same table A then it will return last inserted value by trigger. Because it is working on same table .

SELECT SCOPE_IDENTITY

IDENT_CURRENT()

It returns the last inserted index or identity value for specific table in any connection and any scope. Means if any DML operation performs for particular given table in any session than it will return last inserted index value from that session or scope. It is not limited for any session and scope.

SELECT IDENT_CURRENT(table name)

Let's have a look in below query. Here firstly i created a table and then create a trigger onto that second table for better understanding of these three concepts in a single execution example.

CREATE TABLE Test1( ID int IDENTITY(1,1), Name varchar(50)) CREATE TABLE Test2( ID int IDENTITY(10,1), Name varchar(50), UpdatedTime DATETIME) GO CREATE TRIGGER TestTrigger ON Test1 FOR INSERT AS BEGIN DECLARE @Name varchar(50) SELECT @Name = i.Name from inserted i INSERT into Test2 VALUES (@Name , GETDATE()) END

After the table and trigger creation now time to perform the insertion operation.

Insert into Test1('JP') Insert into Test1('GN')

You can see the clear above picture how my data looks and note that last ID inserted for both tables .Now the result time- write these 3 lines

SELECT @@IDENTITY SELECT SCOPE_IDENTITY SELECT IDENT_CURRENT(Test2)

Here is the final output for these three lines and you clearly differentiate how these are differ from each other