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.

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 (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Error Message:

Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

This error message appears when you try to insert a value into a column for which the IDENTITY property was declared, but without having set the IDENTITY_INSERT setting for the table to ON.

Fix/WorkAround/Solution:

/* Turn Identity Insert ON so records can be inserted in the Identity Column  */
SET IDENTITY_INSERT [dbo].[TableName] ON
GO
INSERT INTO [dbo].[TableName] ( [ID], [Name] )
VALUES ( 2, 'InsertName')
GO
/* Turn Identity Insert OFF  */
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO

Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table. Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table.

SELECT IDENT_SEED(TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

Reference : Pinal Dave (http://blog.SQLAuthority.com)