Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT.

All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

@@IDENTITY
SCOPE_IDENTITY

IDENT_CURRENT

@@IDENTITY-  It returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY- It returns the last identity value generated for any table in the current session and the current scope.
IDENT_CURRENT-It returns the last identity value generated for a specific table in any session and any scope.
GO
CREATE TABLE tbl_Test1
(
 Id int primary key identity(1,1),
 Name varchar(50),
 Email varchar(50)
)

GO
CREATE TABLE tbl_Test2
(
 Id int primary key identity(1,1),
 Name varchar(50),
 Email varchar(50)
)


GO
CREATE PROC usp_InsertTest
(
@Name varchar(10),
@email varchar(10)
)
AS 
BEGIN
insert into tbl_Test2(Name,Email)
VALUES(@Name,@email)

END

GO
BEGIN
INSERT INTO tbl_Test1(Name,Email) VALUES('Sunil','sunil@gmail.com') --THIS INSERT QUERY EXECETED IN CURRENT SCOPE
EXEC dbo.usp_InsertTest 'Sunil','sunil@gmail.com'   -- REECORD Inserted with this Procedure in OUT of this scope
SELECT SCOPE_IDENTITY() 'SCOPE_IDENTITY'  -- Scope_identity() return last Identity of table, inserted in same(LOCAL) scope and same session 
SELECT @@IDENTITY '@@IDENTITY' --  @@identity return last Identity of table, inserted in any(Global Scope) scope and same session (Retrun identity of table record inserted by procedure usp_InsertTest )
SELECT IDENT_CURRENT('tbl_Test1') -- MAY BE DIFFERENT SESSION RETURN LAST IDENTITY OF TABLE PASSED IN PARAMENTER


END