DF069: The SCOPE_IDENTITY() or @@IDENTITY function is used.

Last modified: December 25, 2024

The topic describes the DF069 T-SQL code analysis rule.

Category

EXECUTION RULES

Message

The SCOPE_IDENTITY() or @@IDENTITY function is used.

Description

When queries use parallel execution plans, the identity functions may return incorrect results. Consider using OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY.

Additional information

@@IDENTITY returns the last identity value inserted across any scope in the current session, which can lead to unexpected results if there are triggers or nested stored procedures involved. Similarly, SCOPE_IDENTITY() returns the last identity value inserted in the current scope, which may not always be what is desired.

Both SCOPE_IDENTITY() and @@IDENTITY are session-scoped, which means they are susceptible to concurrency issues in multi-user environments. If multiple concurrent transactions are inserting records, there’s a possibility of getting incorrect or unexpected identity values.

Using @@IDENTITY can lead to errors if there are multiple insert statements in the same scope or if an insert occurs in a trigger. Similarly, if SCOPE_IDENTITY() is used in a trigger or a nested stored procedure, it may return unexpected results.

Instead of using @@IDENTITY or SCOPE_IDENTITY(), it’s generally recommended to use IDENT_CURRENT(‘TableName’) or OUTPUT clause with the INSERT statement to retrieve the identity value in a safer and more controlled manner.

Noncompliant code example

CREATE TABLE dbo.DemoTable(id INT IDENTITY NOT NULL, Name NVARCHAR(128))
GO
 
INSERT INTO dbo.DemoTable(Name)
 
VALUES('DemoTable')
SELECT @@IDENTITY,scope_identity()
GO

Compliant solution

CREATE TABLE dbo.DemoTable(id INT IDENTITY NOT NULL, Name NVARCHAR(128))
GO
DECLARE @identities TABLE(Id INT)
INSERT INTO dbo.DemoTable(Name)
OUTPUT inserted.id INTO @identities
VALUES('DemoTable')
SELECT * FROM @identities
GO