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

Last modified: June 12, 2025

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.

Note

By default, the rule is turned off.

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 CONSTRAINT pk_DemoTable PRIMARY KEY CLUSTERED NOT NULL
, Name NVARCHAR(128) NOT NULL
)
GO
 
INSERT INTO dbo.DemoTable(Name)
VALUES('DemoTable')
 
SELECT SCOPE_IDENTITY()
GO

Compliant solution

CREATE TABLE dbo.DemoTable(
  id INT IDENTITY CONSTRAINT pk_DemoTable PRIMARY KEY CLUSTERED NOT NULL
, Name NVARCHAR(128) NOT NULL
)
GO
 
DECLARE @identities TABLE(
  Id INT NOT NULL
)
 
INSERT INTO dbo.DemoTable(Name)
  OUTPUT INSERTED.id INTO @identities
  VALUES ('DemoTable')
 
SELECT i.Id
FROM @identities i
GO