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

Introduction

In this article, we will see how to use "@@IDENTITY" and "SCOPE_IDENTITY()" in SQL Server. Many sites frequently ask this question about @@IDENTITY and SCOPE_IDENTITY(). Both 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, and Salary. Set the identity property=true for ID. The table looks as in the following.

Now insert some values into this table. The table looks like this.

@@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 set the identity value to true for a column in the table. After that, when we insert data into the 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.

INSERT INTO [dbo].[UserDetail]  
           ([UserName]  
           ,[CompanyName]  
           ,[Salary])  
     VALUES('Ashish','NTPC','15000')  
     Select @@IDENTITY as identityvalue  

Output

Stored procedure with the @@IDENTITY property

Create PROCEDURE [dbo].[InsertUserDetail]  
(  
       @UserName varchar(400),  
       @CompanyName varchar(800),  
       @Salary int,  
       @ReturnBlogId int out  
)  
AS  
BEGIN  
INSERT INTO [dbo].[UserDetail] UserName,CompanyName,Salary  
VALUES(@UserName,@CompanyName,@Salary)  
SET @ReturnBlogId = @@Identity  
END  

Output

SCOPE_IDENTITY() in SQL Server

SCOPE_IDENTITY returns the last identity values generated in any table in the current session. You will always get the value last inserted by your insert statement in the identity column, regardless of whether the insertion happens with your insert statements in any table or if you execute any procedure that does any insertion operation in any table.

Syntax

SCOPE_IDENTITY()

Now, using SCOPE_IDENTITY()

INSERT INTO [dbo].[UserDetail]  
           ([UserName]  
           ,[CompanyName]  
           ,[Salary])  
   VALUES('Ashish','NTPC','15000')  
   SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]  

Output

Stored procedure with @@IDENTITY property

Alter PROCEDURE [dbo].[InsertUserDetail]  
(  
       @UserName varchar(400),  
       @CompanyName varchar(800),  
       @Salary int,  
    @ReturnBlogId int out   
)  
 AS  
BEGIN  
INSERT INTO [dbo].[UserDetail](UserName,CompanyName,Salary)  
VALUES(@UserName,@CompanyName,@Salary)  
SET @ReturnBlogId = scope_Identity()  
END  

Difference Between SCOPE_IDENTITY() and @@IDENTITY

• @@IDENTITY- Returns the last identity values generated in any table in the current session. @@IDENTITY is not limited to a specific scope.
• SCOPE_IDENTITY() - Return the last identity values 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. 

Alter TRIGGER InsertTriger  
ON [UserDetail]  
after INSERT AS  
   BEGIN  
   INSERT [UserTable] VALUES ('Lyon')  
   END  
   go  
INSERT INTO [dbo].[UserDetail]  
           ([UserName]  
           ,[CompanyName]  
           ,[Salary])  
     VALUES('Ashish','NTPC','15000')  
SELECT SCOPE_IDENTITY() AS SCOPEIDENTITYOUTPUT  
SELECT @@IDENTITY AS IDENTITYOUTPUT  
GO  

Output

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.

Conclusion

This article taught us about "@@IDENTITY" and "SCOPE_IDENTITY" in SQL Server.