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_IDNETITY() which is commonly used in place of @@Idnetity 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.

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