SCOPE_IDENTITY() Vs @@ Identity Vs IDENT_CURRENT(tableName) in Sql Server

Hi there, in this blog post i am going to discuss the basic differences between SCOPE_IDENTITY(), @@Identity and IDENT_CURRENT('tableName').
         
      Basically all these three are used to retrieve the last inserted identity value in different session and  different scope. Let's try to understand it in detail.


SCOPE_IDENTITY() :  It returns the value of identity last inserted within same session and same scope.

@@Identity : It is a global variable which returns the value of identity last inserted within same session but in any scope.

IDENT_CURRENT('tableName') : It returns the value of identity last inserted in any session and in any scope.


Now, i am pretty sure you are wondering about what this session and scope really menu in the above mentioned points.
  So, let me clear the same with an example.


First we need to create two different but simple table with having the following structure.

 create table tableA  
 (  
  id int identity(1,1) primary key,  
  value_field nvarchar(100)  
 );

 create table tableB  
 (  
  id int identity(1,1) primary key,  
  value_field nvarchar(100)  
 );

Now, i am inserting some demo rows into tableA

And after insertion my table looks like below screen shot.

Now, run SCOPE_IDENTITY() as observe the result.

 select SCOPE_IDENTITY() as [last inserted id]  

It returned the id of last inserted row (right).
Now execute @@Identity global ( or even both scope_identity() and @@ identity)

Here we can observe both returned same id so whats difference between then. To understand @@Identity property we need to move control outside of current scope.
For that let me create a trigger on insert action of tableA, the trigger is simple going to insert another row into tableB on successfully insertion of data into tableA).

  create trigger insertTrigger on tableA for insert  
      AS  
      BEGIN  
           insert into tableB values('value from trigger')  
      End  

Now, execute another insert query on tableA. and observer message returned by sql server.

Fist  (1 row affected) is due to our insert query and other (1 row affected) from the trigger we written for insertion into tableB.

Now run again both query and see the output (this will clear you the significance of scope).

 select SCOPE_IDENTITY() as [last inserted id using SCOPE_IDENTITY]  
 select @@identity as [last inserted id using @@identity]  

Now, move on to IDENT_CURRENT('tableName')

To under stant IDENT_CURRENT('tbl') we need to create multiple session either by running two instances of SQL Server Management Studio or by opening a new query editor window( iam going with this one).
       And try to insert record into tableB (from second session)

Now get back to session one(previous query editor window). and run the below queries.

 select SCOPE_IDENTITY() as [last inserted id using SCOPE_IDENTITY]  
 select @@identity as [last inserted id using @@identity]  
 select IDENT_CURRENT('tableB')  

observer that IDENT_CURRENT('tableB') returned id 2 which is last inserted id into tableB from out side of current session, where as SCOPE_IDENTITY() returns id of current scope id (i.e. 4) and @@ identity returns id inserted last within same session irrespective to scope(i.e. 2).


For further detailed description you can refer the official documentation by Microsoft : https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15