Symptoms
When you use either SCOPE_IDENTITY() or @@IDENTITY functions to retrieve the values inserted into an identity column, you may notice that these functions sometimes return incorrect values. The problem occurs only when your queries use parallel execution plans. For more information on how to determine if your queries are going to use parallel execution plans refer to the Intra-query parallelism section in the following technical article on Microsoft Downloads:
Cause
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Resolution
Cumulative update information
SQL Server 2008 R2 Service Pack 1
The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1. For more information about how to obtain this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2659694 Cumulative Update package 5 for SQL Server 2008 R2 Service Pack 1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released
Workaround
Microsoft recommends that you do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value as shown in the example below.
Example of using OUTPUT clause:
DECLARE @MyNewIdentityValues table(myidvalues int)
declare @A table (ID int primary key)
insert into @A values (1)
declare @B table (ID int primary key identity(1,1), B int not null)
insert into @B values (1)
select
[RowCount] = @@RowCount,
[@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY()
set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
select
b.ID
from @A a
left join @B b on b.ID = 1
left join @B b2 on b2.B = -1
left join _ddr_T t on t.T = -1
where not exists (select * from _ddr_T t2 where t2.ID = -1)
set statistics profile off
select
[RowCount] = @@RowCount,
[@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENT] = IDENT_CURRENT('_ddr_T')
select * from @MyNewIdentityValues
go
If your situation requires that you do need to use either of these functions, you can use one of the following methods to workaround the issue.
Method 1:
Include the following option in your query
OPTION (MAXDOP 1)
Note: This may hurt the performance of SELECT part of your query.
Method 2:
Read the value from SELECT part into a set of variables (or a single table variable) and then insert into the target table with MAXDOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve the desired performance.
Method 3:
Run the following statement to set the max degree of parallelism option to 1:
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
go
Note: This method may cause performance degradation on the server. You should not use this method unless you have evaluated it in a testing, or staging, environment.
