Difference between IDENT_CURRENT ,@@IDENTITY and SCOPE_IDENTITY ?

 1.When we use the IDENT_CURRENT , it will return the last identity value generated for a specific table in any session and any scope.
2.When we use the @@IDENTITY , it will return the last identity value generated for any table in the current session, across all scopes.
3.When we use the SCOPE_IDENTITY, it will return the last identity value generated for any table in the current session and the current scope.

USE YourDBName
DROP TABLE Table1
DROP TABLE Table2
GO
CREATE TABLE Table1(id int IDENTITY)
CREATE TABLE Table2(id int IDENTITY(10,1))
GO
CREATE TRIGGER trTable1 ON Table1 FOR INSERT 
AS
BEGIN
   INSERT Table2 DEFAULT VALUES
END
GO
--end of 'trTable1'trigger definition

SELECT   * FROM Table1
--id is empty.

SELECT   * FROM Table2
--id is empty.

--Do the following in Session 1
INSERT Table1 DEFAULT VALUES
SELECT @@IDENTITY      
/*Returns the value 10, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()   
// Returns the value 1, which was inserted by the 
INSERT statement 2 statements before this query.

SELECT IDENT_CURRENT('Table2')
// Returns value inserted into Table2, i.e. in the trigger.

SELECT IDENT_CURRENT('Table1')
// Returns value inserted into Table1, which was the INSERT statement 4 stmts before this query.

-- Do the following in Session 2
SELECT @@IDENTITY
//Returns NULL since there has been no INSERT action 
so far in this session.

SELECT SCOPE_IDENTITY()
// Returns NULL since there has been no INSERT action 
so far in this scope in this session.

SELECT IDENT_CURRENT('Table2')
// Returns the last value inserted into Table2.