SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY
RuleId |
SR0008 |
Category |
Microsoft.Design |
Breaking Change |
Non-breaking |
Cause
Your code contains an @@IDENTITY call.
Rule Description
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.
How to Fix Violations
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.
When to Suppress Warnings
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.
Example
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