Systems Engineering and RDBMS

Bug: Scope_Identity() and @@Identity can return wrong values during parallel execution plans

Posted by decipherinfosys on April 4, 2012

Ran into a weird issue today at a client site and upon further researching it, found out that it is a known issue in the SQL Server version that was being used at the client site.  The issue was that at times, we were getting wrong values returned by the Scope_Identity() function.  Upon further diagnosis, we found that the query in question was using a parallel execution plan and when that happens, there is a known issue in some versions of SQL Server because of which you can get wrong Identity value returned.  The KB article from MSFT that talks about this is available here:

http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779

It states that the issue is fixed as part of the Cumulative Update Package 5 of SQL Server 2008 R2 SP1 and any new releases post it.  However, this was a production system and getting any such kind of patch update/upgrade would have taken time so we adopted the workaround of using the OUTPUT clause.   Example of the OUTPUT clause usage is in that KB article and you can also read up more on it in our blog post here and here.  You can also opt for Method 2 mentioned in the workaround in the article.  Method 1 and Method 3 would be sub-optimal for your scenario if the parallel plan was desired to begin with with Method 3 being applied at the Server level and hence effecting all queries.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: