Click or drag to resize

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.

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0117

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
 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

 MessageLineColumn
1SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY.247
2SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY.2520
3SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY.2548
See Also

Other Resources