Comparing Scope_identity (), @@identity and Ident_current

Introduction

Identity is a property in a table. The Identity column values are automatically assigned a value whenever a new record is inserted into a table.

Note. A table can have only one identity column.

In a real scenario, when a new record is inserted into a record, we need to return the last identity value to the end user because the end user application must check whether the record is inserted successfully or not. If we want to return the last identity value, we have 3 built-in statements in SQL Server.

1. scope_identity ()
2. @@identity
3. ident_current

Scope_identity()

This function returns the last identity generated value in the current session and the same current scope.

@@identity

This function returns the last identity generated value in the current regardless of scope.

ident_current

We must pass the table name as an input parameter in this function. It will return the last identity-generated value passed to the table regardless of the session.

Example

--Create Student  
if object_id('Student') is null  
create table Student(id int  identity(1,1) ,Name varchar(20),Marks int)  

In the preceding, the Student table is the Id-Identity column.

Open a new Query window and run this query.

insert into Student(Name,Marks) values('Rakesh',500)  
select scope_identity() as [scope_Identity]    

Open a new Query window and run this query.

select scope_identity() as [scope_Identity]  

@@Identity also generates values based on session and scope. It will return NULL because Scope_identity () returns the Current Session only. Here Current Session means a new session will be created whenever a new window is opened.

But there is a small difference between scope_identity() and @@Identity.

Example. Please truncate all data from Student as in the following.

truncate table Student  
  
-- Create Stored Procdure Sub  
create procedure Ins_Stu_Sub  
(@Name varchar(100),@Marks int)  
as   
begin  
       
     insert into Student(Name,Marks) values(@Name,@Marks)  
      
end  
  
-- Create Stored Procdure Main   
create procedure Ins_Stu_Main  
(@Name varchar(100),@Marks int)  
as   
begin  
       
     insert into Student(Name,Marks) values(@Name,@Marks)  
     exec Ins_Stu_Sub @Name,@Marks  
     select scope_identity() as [scope_identity],@@identity as [identity]  
  
end  
  
--Execute Main Proceure with Values  
exec Ins_Stu_Main 'Rakesh',500  

Here Scope_identity returns the same session and the same scope-generated value. Here Scope-1 means Procedure -1; Scope -2 means Procedure -2, and @@identity returns different scope values also.

ident_current

select ident_current('Student') as[ident_current]  

Here ident_current accepts a table name as an input parameter. It does not depend on the session and scope.

Summary

This article taught us the differences among scope_identity (), @@identity, and ident_current.