SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution

This article is very serious and I would like to explain this as simple as I can. SCOPE_IDENTITY() which is commonly used in place of @@Identity has bug when run in Parallel Plan. You can read my explanation of @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT in earlier article.

The bug is listed here in connect site SCOPE_IDENTITY() sometimes returns incorrect value. Additionally, the bug is also listed in Book Online on last line of the SCOPE_IDENTITY() documentation.

When parallel plan is executed SCOPE_IDENTITY or IDENTITY may produce inconsistent results. The bug will be fixed in future versions of SQL Server. For SQL Server 2008 or any of the earlier version it should be fixed right away using following workarounds.

Single Processor Execution of Query: (Prefered method is to use OUTPUT clause mentioned below)
In short if have multiprocessor and your SQL Server is set to use multiprocessor to execute queries, it is all good till it encounters SCOPE_IDENTITY. You can use (MAX_DOP=1) option to execute query using single processor. As using single CPU can hurt the performance the better solution is to use OUTPUT clause as mentioned below.

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Department
OPTION (MAXDOP 1)
GO

Whole server can be also set to run parallelism in serialization.

sp_configure 'show ;advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Using OUTPUT clause: (Preferred Method)
Refer my previous article for how to use OUTPUT clause information : SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE.I have included the same example along with this article for quick reference.

USE AdventureWorks;
GO
/* Creating the table which will store permanent table */
CREATE TABLE TestTable (ID INT IDENTITY(1,1), TEXTVal VARCHAR(100))
/* Creating temp table to store ovalues of OUTPUT clause */
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
/* Insert values in real table as well use OUTPUT clause to insert
values in the temp table. */
INSERT TestTable (TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES ('FirstVal')
INSERT TestTable (TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES ('SecondVal')
/* Check the values in the temp table and real table
The values in both the tables will be same */
SELECT * FROM @TmpTable
SELECT * FROM TestTable
/* Clean& up */
DROP TABLE TestTable
GO

While executing above code we will get following result. It is very clear from result that Identity which is inserted and which is retrieved using OUTPUT clause is same.

SQL SERVER - 2008 - SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution identitybug

If have you any problem, question about using OUTPUT clause or MAXDOP, please feel free to leave comment here and send me email. I want to make sure that community is aware of this issue and have solution ready. Microsoft will take necessary steps to fix the issue but till then as community members it is our responsibility that we help each other and make sure our applications are running smoothly. Spread the word for this article.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – 2008 – Location of Activity Monitor – Where is SQL Serve Activity Monitor Located
Next Post
SQLAuthority News – Ahmedabad SQL Server User Group Meeting Review – March 21, 2009

Related Posts

27 Comments. Leave new

  • Antonio Espinosa K F C
    August 9, 2015 12:55 am

    Hello Mr. Pinal

    i would like to save in a scalar variable instead a temp table would this be safe? because i don’t like very much temp data

    i thank you for your hardwork ! good work!

    begin tran
    declare @ID int
    set @ID = 0
    insert into T1 values(‘me’)
    output @ID = Inserted.id
    print @ID
    rollback tran

    Reply
  • Jerm Stefanich
    March 24, 2016 1:55 am

    Does @@IDENTITY still have an issue with parallel plans in SQL Server 2012 or 2014? We have several applications that have been using @@IDENTITY and have just realized that the value is getting cross-contaminated thanks to the bug.

    We are debating if easiest fix is to upgrade SQL Server from 2008 R2 (SP2) to 2012/2014 or to go through all the code and either add the MAXDOP option or use OUTPUT clause.

    Reply

Leave a Reply

Menu