You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.


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:

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)
    [RowCount] = @@RowCount,

set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
        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

    [RowCount] = @@RowCount,
select * from @MyNewIdentityValues


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


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
reconfigure with override

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.


Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 2019779 - Last Review: 02/23/2012 02:17:00 - Revision: 4.0

  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • KB2019779
"">var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" ')[0].appendChild(m);" onload="var m=document.createElement('meta');'ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src=""> id=1&t=">