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:
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.