Skip to content

SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY

Identity functions like SCOPE_IDENTITY() and @@IDENTITY can lead to incorrect results when using parallel query execution plans.

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 function
INSERT 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.

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 value
DECLARE @InsertedIds TABLE (Id INT);
INSERT INTO Employees (Name)
OUTPUT INSERTED.Id INTO @InsertedIds
VALUES ('John Doe');
-- Retrieve the last identity value
SELECT Id AS LastIdentity FROM @InsertedIds;

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.

20 minutes per issue.

Design Rules, Bugs

You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY

-- 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 @MyNewIdentityValues
SELECT b.ID
FROM @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=-1
WHERE NOT EXISTS(SELECT *
FROM _ddr_T t2
WHERE t2.ID=-1)
-- @MyNewIdentityValues contains the correct identities
SELECT * FROM @MyNewIdentityValues
-- The analysis rule is voilated here
SELECT SCOPE_IDENTITY()
SELECT [@@IDENTITY]=@@IDENTITY,[SCOPE_IDENTITY]=SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY() -- IGNORE:SA0117
  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

Analysis Rules