SCOPE_IDENTITY() sometimes returns incorrect value

File Name	Submitted By	Submitted On	File Size
broken identity.sql (restricted)	_davedave	2/15/2008	-
Denis result - query 2.txt	Microsoft	3/11/2008	64 KB
broken identity 2.sql (restricted)	_davedave	3/11/2008	-
broken query 2 - results.txt (restricted)	_davedave	3/11/2008	-
broken identity 3 - results.txt (restricted)	_davedave	3/12/2008	-
broken identity 3.sql (restricted)	_davedave	3/12/2008	-
IdentityBug_Data.zip (restricted)	_davedave	3/12/2008	-

Posted by Ian Kemp on 3/27/2012 at 7:38 AM

Microsoft, please confirm if this bug (all scenarios - scope_identity and @@identity) is fixed in SQL 2008 SP3.

Posted by Mini_Ed on 3/21/2012 at 4:05 AM

Allegedly fixed in 2k8 SP3, according to the release notes http://support.microsoft.com/kb/2546951, but we've experienced it with @@IDENTITY as well, no indication if that function is covered off by it. identified as VSTS bug number 778341

Posted by Paul White NZ on 3/8/2012 at 2:29 AM

There is a 'fix' in 2008 R2 SP1 CU5: http://support.microsoft.com/kb/2019779

Not too many details there, sadly.

Posted by andreas.schweizer on 12/7/2011 at 2:27 AM

Our customer has opened a ticket with Microsoft to check the state of this issue. The response from Microsoft was that this bug is fixed in SQL Server 2012, but not in older versions, and that they will probably not provide a patch for older versions of SQL Server because of the risk of regression bugs.

Posted by Nicholas Chammas on 12/1/2011 at 12:36 PM

I second _davedave's questions. What versions of SQL Server do and do not suffer from this bug?

Posted by Alexey Knyazev on 11/25/2011 at 2:39 AM

"Can anyone as MSFT give a bit more info? "

Posted by _davedave on 8/3/2011 at 5:33 PM

I received a notice that this was marked as "fixed", but there is no information on which editions, service packs, cumulative updates, hot fixes, etc will receive the fix.

Can anyone as MSFT give a bit more info?

Posted by Ozzie19 on 7/25/2011 at 11:19 AM

Is this bug still present in SQL 2008 R2?

Posted by D. Kelley on 7/21/2011 at 8:12 AM

Silly ErikEckhardt, when they said "within a couple of weeks (of 6/30/2010)" they were ACTUALLY saying "within a couple of YEARS (of 6/30/2010)." Just relax and give it another year. They'll get to it eventually. I'm sure their executives have priorities that are far more important than those of their customers (i.e., us). So once the SQL Server team has addressed those all-important concerns from their executives (y'know, new features, bell, & whistles to sell), they'll consider addressing minor issues like this (y'know, broken things that are distractions when having good discussions trying to sell new features, bells, & whistles).

I love it when my SQL Server whistles while I work. Now, if I could just rely on it to work CORRECTLY!

Posted by ErikEckhardt on 7/8/2011 at 4:51 PM

"another update on this in the next couple of weeks" must have been meant as *metric* weeks. Does anyone know the conversion factor to Imperial weeks?

Posted by gerleim on 5/6/2011 at 6:13 AM

How can I download the attachments (IdentityBug_Data.zip (restricted))?

Posted by Alexey Knyazev on 4/19/2011 at 11:00 PM

Any update on this?

Posted by warin1g on 2/7/2011 at 5:59 AM

any update on this bug?

Posted by JHudson44 on 10/6/2010 at 7:13 AM

We have witnessed this in our environment as well, and is greatly affecting our ability to use parallelism in our production environment. The last update from Microsoft was on 6/30/2010, can we get another update on the progress of a fix for this?

@@Version = Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Posted by DoriumStorm on 8/18/2010 at 10:55 AM

I've been haunted by that bug for the past week. :@

What a pain.

Posted by erbellico on 8/12/2010 at 2:20 AM

Bummer: Microsoft Entity Framework 4.0 also fell into the trap. It generates code that incorrectly retrieves identity, therefore this makes Entity Framework unusable as soon as you're using IDENTITY property ! :-( What a mess...
See https://connect.microsoft.com/VisualStudio/feedback/details/562148/how-to-avoid-using-identity-scope-based-insert-commands-on-sql-server-2005

Posted by Paula Spinweber on 8/9/2010 at 2:30 PM

Vishal/Microsoft--Do you have a status update on this bug fix since your last post on 6/30/2010? We can reproduce the issue in SQL 2008 and it is severly impacting our clients live environment (ecommerce website). The work-arounds are not an option for us.

Posted by Tony Wright on 8/8/2010 at 7:42 PM

Looking at the workarounds for this, I have a question about one of the options suggested:
"2. Read the value from SELECT part into a set of variables (or single table variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it."

Basically, if I specify a single INSERT using VALUES, surely I wouldn't need MAX_DOP=1? I can't think of a scenario, in this case, where it would ever use parallelism. Can you please confirm whether I still need to specify the MAX_DOP option in this case?

Posted by Microsoft on 6/30/2010 at 1:05 PM

Hi,
Thank you for posting your comments and voting for this issue.

Given the significance of this issue, we have increased its priority and are actively working on fixing it. Please expect another update on this in the next couple of weeks.

Thanks
Vishal

Posted by RyanBarrett on 6/11/2010 at 2:38 AM

Srini Acharya, are you seriously saying that Microsoft don't consider ACID a requirement for your flagship RDBMS?

How am I supposed to make the argument for SQL Server when my customers are comparing it against Oracle? This is the reason why we pick products from the big vendors over the hobbyist open source solutions. It's because we need a high level of realiability, and we trust the vendors to provide it.

Seems that with Microsoft that trust is missplaced.

This attitude makes my question the point of specializing in SQL Server and keeping my certifications up-to-date.

Posted by davisnw on 5/21/2010 at 8:36 AM

The mere fact that Microsoft won't take a data corrupting bug like this seriously makes we wonder about the rest of the product. I will be recommending against using SQL Server for future projects.

Posted by Daniel Smith on 3/3/2010 at 2:59 AM

Any update on this? It's been over two years now with no resolution.

Posted by whiskeypriest on 12/1/2009 at 5:24 PM

Could you post the repro script? I'd like to see if I can duplicate this. Might have the same problem.

Posted by john_s50 on 11/4/2009 at 2:07 PM

How can I get the attachments to test the issue on my system?
What is the latest on this? Is it fixed in SQL 2008? SP1?

Posted by JAlbanator on 10/26/2009 at 11:06 AM

I recently upgraded from 2000 to 2005 and, like others, SCOPE_IDENTITY() was not working appropriately as well. I found a workaround with our implementation by using the following:

SET NOCOUNT ON SELECT SCOPE_IDENTITY()

I hope this helps somebody.

Posted by Daniel Smith on 10/19/2009 at 1:59 AM

I realise this is mainly a corner case, but it puts a seed of doubt in people's minds. I don't want to have to always be checking my back to see if a feature I'm using in the product has a potentially horrendous data corrupting bug.

Please fix this before people loose all trust in the product.

Posted by ErikEckhardt on 9/29/2009 at 5:19 PM

Quote: >99% of use cases for SCOPE_IDENTITY and @@IDENTITY fall into the pattern of insert a single value, and get the id

Not for me. In one database I developed, the almost exclusive use of Scope_Identity() was in combination with @@RowCount to get the entire range of inserted rows and then perform some additional operation with that range. Just because the function returns the last row value does not mean that only the last row is of interest.

This seems like a really, really nasty bug and I think it should be addressed in some way as a high priority. Even if the problem can't be fixed so soon, how about a procedure-creation-time warning when any of these functions are in the script text, saying that they can return the wrong values if parallelism occurs?

Posted by Paul White NZ on 9/12/2009 at 9:26 PM

Is IDENT_CURRENT affected in the same way?

Posted by SQL_Guru on 5/28/2009 at 10:12 AM

dave_dave: You should open a case with Microsoft on this. Cases for bugs are free and maybe they'll fix it then.

I really think this needs to be fixed in any case. This affects the data integrity by possibly having data point to the wrong row. If you don't think this is important, please ask some of the banks using SQL Server and see if having foreign keys point to the wrong row matters. If they find out that they have misplaced money because of this and MS won't fix it, be sure that they will be switching from SQL as fast as they can.

It needs to be fixed in both SQL 2005 and SQL 2008. Just because SQL 2005 is older, doesn't mean the data in those databases is less important. SQL 2005 is still in mainstream support until April, 2011. Until then it should still get these fixes, especially this type were it affects data integrity.

Posted by Brandon Reno1 on 3/20/2009 at 8:45 AM

Don't get me wrong, while I'm sure there is code out there that may need to beware of this, >99% of use cases for SCOPE_IDENTITY and @@IDENTITY fall into the pattern of insert a single value, and get the id. From what I can tell, inserts using the VALUES clause are not affected:

INSERT INTO Table (fld1, fld2) VALUES(@v1, @v2)
SET @id = SCOPE_IDENTITY()

this does not get a parallel plan, and would not be affected.

If you are doing a select, and expecting only one record, I hope that you are either checking against a PK or UNIQUE constraint or using TOP 1 (from what I can see, neither of which ever creates parallelized execution plans, though I could be wrong.)

I did a quick scan of our code, and could not find an example that could possibly be affected.

Posted by Mike C_1 on 3/19/2009 at 10:01 PM

This is not a bug to be taken lightly. This thing affects huge amounts of code already in place and could cause severe problems and as Denis pointed out it will cause "plenty of headaches for many people." Because of the amount of existing code out there using SCOPE_IDENTITY() and @@IDENTITY I would consider this an extremely high priority/high severity defect.

Posted by Microsoft on 6/18/2008 at 12:13 PM

Fair enough. I have made a note of need for documentation in our bug tracking database.

Denis.

Posted by _davedave on 6/18/2008 at 12:06 PM

Because this can (and has) resulted in data loss, you should at least update the SQL 2005 and 2008 help files to indicate that we should not be using @@Identity or SCOPE_IDENTITY() without including "option (maxdop 1)".

Posted by Microsoft on 6/18/2008 at 11:55 AM

Unfortunately, after evaluating our fix options we have come to conclusion that we can't fix this for SQL 2008. We're not doing this lightly, I have spent close to a month trying to make a robust fix for this, but amount of required work did not fit into this release.

If you come across this issue you will have to resort to one of the workarounds I have put in my previous post.

Posted by Microsoft on 3/18/2008 at 1:10 PM

Dave, thanks to your very detailed and dilligent report I was able to find the problem. Yes, it's a bug - whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can't be relied upon. The few workarounds I can offer you for now:

1. Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query.
2. Read the value from SELECT part into a set of variables (or single tabel variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it.
3. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I highly recomend using OUTPUT instead of @@IDENTITY in all cases. It's just the best way there is to read identity and timestamp.
4. Changing autostas is NOT a good workaround. It may hide the problem for a while but a prallel plan will get produced eventually.
5. Force serial plans for entire server via sp_configure 'max degree of parallelism' option.

Now about life beyond workaround. We will fix it in SQL 2008. If you need this to be fixed also in SQL 2005 you will have to request a QFE through official support channels. I can't promise anything either, as usual it's a factor of complexity of request, resource availability, quality of workarounds etc.

In the end, thank you once again for reporting this. You have saved plenty of headaches for many people.

Denis Altudov, Microsoft.

=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
             Example as promised:
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

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)
--option (maxdop 1) --!!! If you uncomment this line, it works
set statistics profile off

select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY(),
    [IDENT_CURRENT] = IDENT_CURRENT('_ddr_T')
select * from @MyNewIdentityValues
go
=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/

Posted by _davedave on 3/12/2008 at 7:16 AM

I've attached IdentityBug_Data.zip that contains IdentityBug_Data.mdf

I did not include the log file because even when zipped, it is larger than 10MB. If you need it, let me know.

I preparted the database for uploading using the attached
Broken Identity 3.sql
which output
Broken Identity 3 - Results.txt

Posted by _davedave on 3/11/2008 at 6:29 PM

I see that you ran your tests using the developer edition. Can you retry using Enterprise and Standard editions?

I'll attach the database tomorrow.

Posted by Microsoft on 3/11/2008 at 5:47 PM

Dave, thanks for your patience.

I have forced paralellism on my machine and re-run your query.
Now query plans looks nearly identical, yet the result is still different - same as I had last time.
See entire result attached as "Denis result - query 2.txt"

Maybe you could try to attach the database itself to this bug?

This seems the best way to make progress now.

Denis.

Posted by _davedave on 3/11/2008 at 7:34 AM

If I change the AUTO_CREATE_STATISTICS setting, it works -- although I'm not convinced this is directly related to the problem. I suspect it is just because this causes a change in the query plan. I've attached a new script and the output that shows the change it causes in the query plan. My guess is that Parallelism is what is breaking the identity functions.

When the script is run using Express (where it always returns the correct results), I can see that parallelism is not being used.

It also returns the correct results on the Enterprise edition if I add either "OPTION (MAXDOP 1)" or "TOP 1" -- both of which cause parallelism to be dropped from the plan.

Posted by _davedave on 3/11/2008 at 6:29 AM

I tried it on another client's system -- one that I was not involved in configuring -- and it returned the incorrect results. Their @@Version=
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
    Feb 9 2007 22:47:07
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Posted by _davedave on 3/11/2008 at 6:20 AM

The script returns the correct results when run on a machine with @@Version =
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

Posted by _davedave on 3/11/2008 at 6:17 AM

It also breaks when I run the script on another of our servers. This one with @@Version =
Microsoft SQL Server 2005 - 9.00.3152.00 (Intel X86) Mar 3 2007 03:17:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Posted by _davedave on 3/11/2008 at 6:15 AM

When I run the script, it returns something different than when you ran it. I get:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1000000 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
RowCount    @@IDENTITY                             SCOPE_IDENTITY
----------- --------------------------------------- ---------------------------------------
1         1                                     1

(1 row(s) affected)

(1 row(s) affected)

RowCount    @@IDENTITY                             SCOPE_IDENTITY                         IDENT_CURRENT
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1         1                                     1                                     1000001

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
RowCount    @@IDENTITY                             SCOPE_IDENTITY
----------- --------------------------------------- ---------------------------------------
1         1                                     1

(1 row(s) affected)

(1 row(s) affected)

RowCount    @@IDENTITY                             SCOPE_IDENTITY                         IDENT_CURRENT
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1         1                                     1                                     1000002

(1 row(s) affected)

Posted by Microsoft on 3/10/2008 at 7:02 PM

Dave, I have run your repro script using both "SQL Management Studio" as well as "osql.exe" on build 9.00.3159.00 and it produced the result I would expect it to produce, added below. Thsi result is by design.

Are you seeing a different result?

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1000000 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
RowCount    @@IDENTITY                             SCOPE_IDENTITY
----------- --------------------------------------- ---------------------------------------
1         1                                     1

(1 row(s) affected)

(1 row(s) affected)

RowCount    @@IDENTITY                             SCOPE_IDENTITY                         IDENT_CURRENT
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1         1000001                                 1000001                                 1000001

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
RowCount    @@IDENTITY                             SCOPE_IDENTITY
----------- --------------------------------------- ---------------------------------------
1         1                                     1

(1 row(s) affected)

(1 row(s) affected)

RowCount    @@IDENTITY                             SCOPE_IDENTITY                         IDENT_CURRENT
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1         1000002                                 1000002                                 1000002

(1 row(s) affected)

Posted by Microsoft on 2/28/2008 at 9:13 AM

Hi Dave,
Thanks for providing this feedback about a possible issue with the Scope_identity function. We are investigating this issue to see if this is a defect that needs to be fixed. We will keep you updated on the resolution to this issue as soon as it gets resolved. Thanks again for narrowing down this issue and providing the details.

Srini Acharya
Relational Engine

Posted by Matthew Cenance on 7/16/2010 at 5:47 PM

InafedW qt 23[]3 w4

Posted by erbellico on 5/26/2010 at 10:12 AM

A KB article exists, that is a mere copy-paste of this issue :-(
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779
Yet it's approximately as unclear as this article. As they say "this is a fast-publish article". Well, fix it up now ! This bug has been creeping for years...

Posted by erbellico on 3/30/2010 at 8:46 AM

Complementing JAlbabanator's workaround :
SET NOCOUNT OFF SELECT SCOPE_IDENTITY() also works (seems that either ON or OFF prevents from bugging). This is to confirm, though. I have not run thorough testing :-(

Posted by imannoee on 1/12/2010 at 2:50 PM

We've caught both SCOPE_IDENTITY() and @@IDENTITY returning the incorrect value after doing an insert. It appears to be some combination of our database settings, the number of rows in the tables, and the particulars of the query plan. I've managed to write a script that will show the incorrect behavior. It will create a new database, create a table with 1 million rows, and then show the incorrect behavior.

@@Version =
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86)
     Mar 23 2007 16:15:11
     Copyright (c) 1988-2005 Microsoft Corporation
     Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Posted by JAlbanator on 10/26/2009 at 11:04 AM

Posted by Tony Oz on 7/8/2009 at 5:40 PM

The following code snippet shows more detail on how to use the OUTPUT statement to return the Identity column of the newly inserted record. Because we use a Trigger on insert the OUTPUT value cannot just be returned, it must be used in conjunction with the INTO statement.

ID is the identity column which is not inserted but is retreived using the OUTPUT statement

    Declare @InsertedData table (ID int)
    INSERT INTO Orders (VendorOrderID, StoreID, DateCreated)
        OUTPUT Inserted.ID
        INTO @InsertedData
    VALUES (@VendorOrderID, @StoreID, @DateCreated)
    SELECT ID FROM @InsertedData

The table @InsertedData can be expanded to include more columns as required to retreive the Orders table inserted values.

Posted by eschudy on 6/19/2009 at 12:57 AM

What about using IDENT_CURRENT('tablename') as in this post?
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

I recently change our code to use this and seem to have solved the problem.

Posted by Kevin Wagner on 3/31/2009 at 5:16 PM

USE the output function of the insert statement to return the value.

SCOPE_IDENTITY() sometimes returns incorrect value by _davedave