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

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

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.