Article ID: 2019779 - View products that this article applies to.
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:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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
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:
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.
Include the following option in your query
Note: This may hurt the performance of SELECT part of your query.
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.
Run the following statement to set the max degree of parallelism option to 1:
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.
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2019779 - Last Review: February 23, 2012 - Revision: 4.0
Contact us for more help
Connect with Answer Desk for expert help.