SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY

Introduction

The problem of using @@IDENTITY is that it can lead to inaccurate results when retrieving the last assigned identity value.

Description

The use of @@IDENTITY can cause issues in T-SQL and SQL Server because it is not restricted to the current session or scope. This means it may return an identity value generated by a trigger or a different session, leading to inconsistent data retrieval, especially if the column is involved in a replication article.

For example, problematic query using @@IDENTITY:

INSERT INTO Employees (Name) VALUES ('John Doe');
SELECT @@IDENTITY AS LastInsertedID;

In this example, @@IDENTITY may return the identity value from another session’s triggered operation instead of the one just inserted. This can result in incorrect business logic outcomes or data integrity issues, especially in environments with triggers or multiple transactions.

Potential for retrieving an incorrect identity value from a different session or triggered operation.
Risk of compromising data integrity in replicated environments involving identity columns.

How to fix

To ensure accurate retrieval of the last inserted identity value, use SCOPE_IDENTITY() instead of @@IDENTITY in your T-SQL queries.

Follow these steps to address the issue:

1.Identify all occurrences of @@IDENTITY in your queries.

2.Replace @@IDENTITY with SCOPE_IDENTITY() to ensure the identity value is retrieved from the same scope and session.

3.Test your updated queries to confirm that the correct identity value is being retrieved without affecting existing business logic or data integrity.

Corrected query using SCOPE_IDENTITY():

INSERT INTO Employees (Name) VALUES ('John Doe');
SELECT SCOPE_IDENTITY() AS LastInsertedID;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- SA0012 analysis rule is voilated here
SELECT @@IDENTITY

-- OK
SELECT SCOPE_IDENTITY()

Example Test SQL with Automatic Fix

-- SA0012 analysis rule is voilated here
SELECT SCOPE_IDENTITY()

-- OK
SELECT SCOPE_IDENTITY()

Analysis Results

	Message	Line	Column
1	SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY.	2	7

Analysis Rules