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

How to find last generated identity column value in SQL Server

Please go through the below before proceeding:

Identity Column in SQL Server

For retrieving identity column values first create a table. 

Create a table using Identity Key:

CREATE TABLE tblPerson (

    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,

    [Name] varchar(50),

    [Gender] CHAR(1)

);

INSERT INTO tblPerson([Name], [Gender])

values

('John Doe','M'),

('Kimi Sharma','F')

We can get last generated Identity values in two ways

Using function SCOPE_IDENTITY() and global variable @@IDENTITY

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('tblPerson')

Output: 2

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.

You can find the difference Here:

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