Friday, February 03, 2012

Avoid using SCOPE_IDENTITY and @@IDENTITY

Avoid using SCOPE_IDENTITY() and @@IDENTITY functions if your system is using Parallel Plans.


Extract from the above link:
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.

Check out this KB Article for more information on this. As of SQL Server 2008 R2 this issue is not yet resolved and am hopeful that this would be fixed in SQL Server 2012.

No comments: