Select the product you need help with

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

Article ID: 2019779 - View products that this article applies to.

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:

Troubleshooting Performance Problems in SQL Server 2005

Back to the top | Give Feedback

Cause

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

More Information

Back to the top | Give Feedback

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

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback