Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

Note: Scope is a module; a Stored Procedure, trigger, function, or batch

Example:

create table Test1(

ID int identity(1,1),

value varchar(30)

)

create table Test2(

ID int identity(1,1),

value varchar(30)

)

insert into Test1

values ('RK'),('MK')

select SCOPE_IDENTITY()

select @@IDENTITY

Now create a trigger in Test1 table

create trigger insertonTest1 on Test1 for Insert

as

Begin

insert into Test2 values ('GK')

End

Now insert new record to Test1 table

insert into Test1

values ('SHG')

Now the below scripts on the same session

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Now you can see @@IDENTITY returned the value from different scope. Also IDENT_CURRENT returns as per table name.

Now run the below query on different window means different session.

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Since SCOPE_IDENTITY and @@IDENTITY run on different session returned NULL But as the same time IDENT_CURRENT returns the value irrespective of session and scope

You can find other details of Identity columns Below

• Identity Column in SQL Server

• How to find last generated identity column value in SQL Server