"@@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:

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

@@IDENTITY Property

@@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:

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()

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 for 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 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:

 

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.