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.

  1. --Create Student  
  2. if object_id('Student'is null  
  3. 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:
  1. insert into Student(Name,Marks) values('Rakesh',500)  
  2. select scope_identity() as [scope_Identity]    

Open a new Query window and run this query:
  1. 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:
  1. truncate table Student  
  3. -- Create Stored Procdure Sub  
  4. create procedure Ins_Stu_Sub  
  5. (@Name varchar(100),@Marks int)  
  6. as   
  7. begin  
  9.      insert into Student(Name,Marks) values(@Name,@Marks)  
  11. end  
  13. -- Create Stored Procdure Main   
  14. create procedure Ins_Stu_Main  
  15. (@Name varchar(100),@Marks int)  
  16. as   
  17. begin  
  19.      insert into Student(Name,Marks) values(@Name,@Marks)  
  20.      exec Ins_Stu_Sub @Name,@Marks  
  21.      select scope_identity() as [scope_identity],@@identity as [identity]  
  23. end  
  25. --Execute Main Proceure with Values  
  26. 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.

  1. 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.