Saturday, August 29, 2020

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


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 @@IDENTITY

Now create a trigger in Test1 table

create trigger insertonTest1 on Test1 for Insert



insert into Test2 values ('GK')


Now insert new record to Test1 table

insert into Test1

values ('SHG')

Now the below scripts on the same session


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 @@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

No comments:

Post a Comment