Comparing Scope_identity (), @@identity and Ident_current

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 that the record is inserted successfully or not. If we want to return the last identity value then 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 genarated value in the current session and the same current scope.

@@identity: This function returns the last identity geanrated value in the current and regardless of scope.
ident_current: This function we need to pass the table name as an input parameter. It will return the last identity genarated value passed to the table regarless of 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]

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

But there is a small diffrence 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 input parameter . It does not depend on session and scope.