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

The topic describes the SA0117 analysis rule.

Message

Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY

Description

The rule check the code for using any of SCOPE_IDENTITY() or @@IDENTITY functions.

When the queries use parallel execution plans, the identity functions may return incorrect results.

How to fix

It is recommended to use the OUTPUT syntax if correct value is wanted.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1-- Example of recommeded Output usage
 2
 3DECLARE @MyNewIdentityValues TABLE(myidvalues INT)
 4DECLARE @A TABLE(ID INT PRIMARY KEY)
 5DECLARE @B TABLE(ID INT PRIMARY KEY IDENTITY(1,1),B INT NOT NULL)
 6
 7INSERT INTO @A VALUES (1)
 8INSERT INTO @B VALUES (1)
 9
10INSERT INTO TestTable1 OUTPUT inserted.ID INTO @MyNewIdentityValues
11SELECT b.ID
12FROM @A a
13     LEFT JOIN @B b ON b.ID=1
14     LEFT JOIN @B b2 ON b2.B=-1
15     LEFT JOIN TestTable1 t ON t.T=-1
16WHERE NOT EXISTS(SELECT *
17                 FROM _ddr_T t2
18                 WHERE t2.ID=-1)
19
20-- @MyNewIdentityValues contains the correct identities
21SELECT * FROM @MyNewIdentityValues
22
23-- The analysis rule is voilated here
24SELECT SCOPE_IDENTITY() 
25SELECT [@@IDENTITY]=@@IDENTITY,[SCOPE_IDENTITY]=SCOPE_IDENTITY()
26
27SELECT SCOPE_IDENTITY() -- IGNORE:SA0117
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
See Also

Other Resources

Analysis Rules