SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY

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:

1INSERT INTO Employees (Name) VALUES ('John Doe');
2SELECT @@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.

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

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

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources