Export (0) Print
Expand All

SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY

RuleId

SR0008

Category

Microsoft.Design

Breaking Change

Non-breaking

Your code contains an @@IDENTITY call.

Because @@IDENTITY is a global identity value, it might have been updated outside the current scope and obtained an unexpected value. Triggers, including nested triggers used by replication, can update @@IDENTITY outside your current scope.

To resolve this issue you must replace references to @@IDENTITY with SCOPE_IDENTITY, which returns the most recent identity value in the scope of the user statement.

You might suppress this warning if the statement that uses @@IDENTITY is used when you are sure that no other processing might have updated the value of @@IDENTITY. However, we strongly recommend that you resolve the warning instead of suppressing it because SCOPE_IDENTITY provides the intended value without the risk of unexpected changes.

In the first example, @@IDENTITY is used in a stored procedure that inserts data into a table. The table is then published for merge replication, which adds triggers to tables that are published. Therefore, @@IDENTITY can return the value from the insert operation into a replication system table instead of the insert operation into a user table.

The Sales.Customer table has a maximum identity value of 29483. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() returns the value from the insert operation into the user table, but @@IDENTITY returns the value from the insert operation into the replication system table.

The second example shows how you can use SCOPE_IDENTITY() to access the inserted identity value and resolve the warning.

CREATE PROCEDURE [dbo].[ProcedureWithWarning]
@param1 INT, 
@param2 NCHAR(1),
@Param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = @@IDENTITY
END

CREATE PROCEDURE [dbo].[ProcedureFixed]
@param1 INT, 
@param2 NCHAR(1),
@param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = SCOPE_IDENTITY()
END

Community Additions

ADD
Show:
© 2015 Microsoft