In most of our applications, We need last inserted identity value in SQL Server table. For getting the last inserted identity value we have multiple options:

1. @@IDENTITY
2. SCOPE_IDENTITY
3. IDENT_CURRENT

All these three return the last inserted identity value in table but there value is differ based on session and scope

@@IDENTITY:-

            @@IDENTITY is a global variable which returns the identity in current session and across the scope(i.e. global scope.).@@IDENTITY is limited to the current session, it is not limited to the current scope. Suppose that we have Insert trigger on table which causes the identity to be created on another table then @@IDENTITY returns that identity record which is created by trigger not from the table which triggered the insert trigger.

            SCOPE_IDENTITY is a function which will return the Identity from current session but it will also limit it to your current scope as well.It will return the identity created by table not by the trigger on that table

             IDENT_CURRENT is a function which returns the last inserted identity for specific table in any session and in any scope.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. In other words, it is not affected by any scope and any session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

Example:-         I have created the table1,table2 and insert trigger on table1 . Please refer Below

CREATE TABLE Table1(id int IDENTITY);    
CREATE TABLE Table2(id int IDENTITY(50,1));    
GO
CREATE TRIGGER Inserttrigger ON Table1 FOR INSERT    
AS   
BEGIN   
   INSERT Table2 DEFAULT VALUES    
END;    
GO

SELECT id FROM table1;
--id is empty.

SELECT id FROM table2;
--Id is empty. 

INSERT table1 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 50. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('table2');
/* Returns value inserted into table2, that is in the trigger.*/

SELECT IDENT_CURRENT('table1');
/* Returns value inserted into table1. 
This was the INSERT statement four statements before this query.*/ 

Do the following in new session(In new window)

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('table2');
/* Returns the last value inserted into table2.*/