How to get last identity column value from table. Different between @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT

How to get last identity column value from table. Different between @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT

	Session	Scope
@@IDENTITY	Same Session	Any  scope value
SCOPE_IDENTITY()	Same Session	Local scope value
IDENT_CURRENT()	May be different	Depends on table name passed in parameter

Discription:

SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).

use Test

--First Table

create table Test1

(

Id int identity(1,1),

Name varchar(50)

)

--Second Table

create table Test2

(

Id int identity(1,1),

Name varchar(50)

)

insert into Test1 values('Kush')

insert into Test1 values('Lav')

select * from Test1

--How to get last identity column value from table

select SCOPE_IDENTITY()

--Result=   2

select SCOPE_IDENTITY()

select @@IDENTITY

--Result=   2,2

--create  trigger  on Test1 Table

create trigger tr1 on Test1

after insert

as

begin

insert into Test2 values('yyyyy')

end

--select  record  from both table

select *  from test1 -- 2 record

select *  from test2  -- blank

insert  into test1 values ('Dinesh singh')

select SCOPE_IDENTITY()

select @@IDENTITY

--Result 3 ,1

Note :  SCOPE_IDENTITY(),@@IDENTITY both are return last generated column from table but  different  b/w SCOPE_IDENTITY() work in with same connection or same session and same scope  , @@IDENTITY work in same session and across any scope 

select SCOPE_IDENTITY()

select @@IDENTITY

SELECT IDENT_CURRENT('Test1')

SELECT IDENT_CURRENT('Test2');

--Result – 3 1 3 1