"@@IDENTITY" and "SCOPE_IDENTITY" in SQL Server 2012

In this article, we will see how to use @@IDENTITY and SCOPE_IDENTITY() in SQL Server 2012.

In this article, we will see how to use "@@IDENTITY" and "SCOPE_IDENTITY()" in SQL Server 2012. This is a question which is frequently asked in many sites about @@IDENTITY and SCOPE_IDENTITY(). Both are used to return the last inserted identity value in the current session. Here, we will see the difference between them. So let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Creating Table in SQL Server Database

 
Now create a table named UserDetail with the columns ID, UserName, CompanyName, Salary. Set the identity property=true for ID. The table looks as in the following:
 
Table-Identity-in-SQLServer.jpg
 
Now insert some values into this table. The table looks like this:
 
Table-in-SqlServer.jpg
 

@@IDENTITY Property in SQL Server

 
@@IDENTITY will return the last identity value entered into a table. @@IDENTITY is not limited to a specific scope. Suppose we create a table and the set identity value to true for a column in the table. After that when we insert data into table we get the last identity value using @@IDENTITY. If the statement did not affect any tables with identity columns, then @@IDENTITY returns NULL. You can also access the @@IDENTITY value in your application.
 
Now for using the @@IDENTITY property:
  1. INSERT INTO [dbo].[UserDetail]  
  2.            ([UserName]  
  3.            ,[CompanyName]  
  4.            ,[Salary])  
  5.      VALUES('Ashish','NTPC','15000')  
  6.      Select @@IDENTITY as identityvalue  
OUTPUT
 
@@IDENTITY-in-SQLServer.jpg@@IDENTITY Property in SQL Server
 
Stored procedure with the @@IDENTITY property
  1. Create PROCEDURE [dbo].[InsertUserDetail]  
  2. (  
  3.        @UserName varchar(400),  
  4.        @CompanyName varchar(800),  
  5.        @Salary int,  
  6.        @ReturnBlogId int out  
  7. )  
  8. AS  
  9. BEGIN  
  10. INSERT INTO [dbo].[UserDetail] UserName,CompanyName,Salary  
  11. VALUES(@UserName,@CompanyName,@Salary)  
  12. SET @ReturnBlogId = @@Identity  
  13. END  
OUTPUT
 
@@IDENTITY Property in SQL Server
 

SCOPE_IDENTITY() in SQL Server

 
SCOPE_IDENTITY returns the last identity values that were generated in any table in the current session. You will always get the value that was last inserted by your insert statement in the identity column, regardless of whether the insertion happens with your insert statements in any table or you execute any procedure that is doing any insertion operation in any table.
 
Syntax
 
SCOPE_IDENTITY()
 
Now, using SCOPE_IDENTITY()
  1. INSERT INTO [dbo].[UserDetail]  
  2.            ([UserName]  
  3.            ,[CompanyName]  
  4.            ,[Salary])  
  5.    VALUES('Ashish','NTPC','15000')  
  6.    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]  
OUTPUT
 
SCOPE_IDENTITY() in SQL Server
 
Stored procedure with @@IDENTITY property
  1. Alter PROCEDURE [dbo].[InsertUserDetail]  
  2. (  
  3.        @UserName varchar(400),  
  4.        @CompanyName varchar(800),  
  5.        @Salary int,  
  6.     @ReturnBlogId int out   
  7. )  
  8.  AS  
  9. BEGIN  
  10. INSERT INTO [dbo].[UserDetail](UserName,CompanyName,Salary)  
  11. VALUES(@UserName,@CompanyName,@Salary)  
  12. SET @ReturnBlogId = scope_Identity()  
  13. END  

Difference Between SCOPE_IDENTITY() and @@IDENTITY

 
@@IDENTITY - Returns the last identity values that were generated in any table in the current session. @@IDENTITY is not limited to a specific scope.
 
SCOPE_IDENTITY() - Return the last identity values that are generated in any table in the current session. SCOPE_IDENTITY returns values inserted only within the current scope.
 
Example
 
This example defines how they generate a different identity value. Let us suppose we have two tables named UserDetail and UserTable. And we have one trigger defined on UserDetail that inserts a record into UserTable when a new record is inserted into UserDetail. See:
  1. Alter TRIGGER InsertTriger  
  2. ON [UserDetail]  
  3. after INSERT AS  
  4.    BEGIN  
  5.    INSERT [UserTable] VALUES ('Lyon')  
  6.    END  
  7.    go  
  8. INSERT INTO [dbo].[UserDetail]  
  9.            ([UserName]  
  10.            ,[CompanyName]  
  11.            ,[Salary])  
  12.      VALUES('Ashish','NTPC','15000')  
  13. SELECT SCOPE_IDENTITY() AS SCOPEIDENTITYOUTPUT  
  14. SELECT @@IDENTITY AS IDENTITYOUTPUT  
  15. GO  
OUTPUT
 
SCOPE_IDENTITY() vs @@IDENTITY
 
The preceding output shows SCOPE_IDENTITY returned the last identity value in the same scope. @@IDENTITY returned the last identity value inserted to UserDetail by the trigger.