SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY
Introduction
Section titled “Introduction”Identity functions like SCOPE_IDENTITY() and @@IDENTITY can lead to incorrect results when using parallel query execution plans.
Description
Section titled “Description”Functions SCOPE_IDENTITY() and @@IDENTITY are used to retrieve the last identity value generated in the current session or scope. While SCOPE_IDENTITY() is considered safer than @@IDENTITY , both can return incorrect results when queries are executed with parallel execution plans in SQL Server.
For example:
-- Example of potentially problematic use of identity functionINSERT INTO Employees (Name)VALUES ('John Doe');SELECT SCOPE_IDENTITY() AS LastIdentity;In a parallel execution scenario, the identity value returned by SCOPE_IDENTITY() might not reflect the correct value, especially if the parallel thread context affects scope perception.
-
Misreporting of the last inserted identity value can lead to data inconsistencies, especially in transactions involving related tables.
-
Such incorrect values can hamper logic that relies on identity values for creating relationships or dependencies.
How to fix
Section titled “How to fix”Use the OUTPUT clause to reliably obtain the last identity value generated, thus avoiding issues caused by parallel execution plans.
Follow these steps to address the issue:
1.Modify your INSERT statement to include the OUTPUT clause. This clause allows capturing inserted identity values directly into a variable or table.
2.Ensure that the OUTPUT clause specifies the desired identity column for the returned values.
3.Use a table variable or a temporary table to store the output of the INSERT operation, enabling additional logical operations or retrieval as needed.
For example:
-- Example using the OUTPUT clause to get the last inserted identity valueDECLARE @InsertedIds TABLE (Id INT);
INSERT INTO Employees (Name)OUTPUT INSERTED.Id INTO @InsertedIdsVALUES ('John Doe');
-- Retrieve the last identity valueSELECT Id AS LastIdentity FROM @InsertedIds;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY
Example Test SQL
Section titled “Example Test SQL”-- Example of recommeded Output usage
DECLARE @MyNewIdentityValues TABLE(myidvalues INT)DECLARE @A TABLE(ID INT PRIMARY KEY)DECLARE @B TABLE(ID INT PRIMARY KEY IDENTITY(1,1),B INT NOT NULL)
INSERT INTO @A VALUES (1)INSERT INTO @B VALUES (1)
INSERT INTO TestTable1 OUTPUT inserted.ID INTO @MyNewIdentityValuesSELECT b.IDFROM @A a LEFT JOIN @B b ON b.ID=1 LEFT JOIN @B b2 ON b2.B=-1 LEFT JOIN TestTable1 t ON t.T=-1WHERE NOT EXISTS(SELECT * FROM _ddr_T t2 WHERE t2.ID=-1)
-- @MyNewIdentityValues contains the correct identitiesSELECT * FROM @MyNewIdentityValues
-- The analysis rule is voilated hereSELECT SCOPE_IDENTITY()SELECT [@@IDENTITY]=@@IDENTITY,[SCOPE_IDENTITY]=SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY() -- IGNORE:SA0117Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY. | 24 | 7 |
| 2 | SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY. | 25 | 20 |
| 3 | SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY. | 25 | 48 |