SQL Server : what is @@IDENTITY, IDENT_CURRENT and SCOPE_IDENTITY()

In Sql Server, This is very big confusion on @@IDENTITY, @IDENT_CURRENT and SCOPE_IDENTITY() .

I always face this question in my interview.

Let's know about it

@@IDENTITY, @IDENT_CURRENT and SCOPE_IDENTITY()  are similar functions, always returns last generated identity value.


@@IDENTITY

@@IDENTITY always return the last generated identity value which is inserted in identity column in current session. it is not depend on scope.

If any INSERT , BulkCopy or SELECT INTO command is fired then @@IDENTITY contains the last generated identity value.

If above operation doesn't affect identity column then @@IDENTITY returns NULL.

If multiple INSERT operation executed in same table or different table then @@IDENTITY returns Last Generated identity value.

Insert into T1
(name,address,mobile)
values('Ajay','Delhi','90000001')

Insert into T2
(name,address,mobile)
values('Vijay','Chandigarh','90010001')

Insert into T3
(name,address,mobile)
values('Manish','Gurgaon','90002001')


select @@IDENTITY
-- @@IDENTITY reruns Last generated identity
-- value from Table T3

IDENT_CURRENT

IDENT_CURRENT always returns the last generated identity value for a specific table.

select IDENT_CURRENT('TABLENAME')

It doesn't depend on session and scope.

SCOPE_IDENTITY()

SCOPE_IDENTITY() always returns the last generated identity value in current session and current scope.

select SCOPE_IDENTITY()

SCOPE like procedures, functions, triggers etc.