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

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

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

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.

More Information

Microsoft Connect bug on this issue

Max Degree of Parallelism (MAXDOP)