Use of Identity Property to Resolve Concurrency Issues

• alishahryar

  SSC Journeyman

  Points: 79

  More actions

  March 26, 2013 at 2:00 pm

  #1600955

  I came to add exactly the same point - SCOPE_IDENTITY() would guarantee that the just-inserted rows' newly-generated IDENTITY value would be unique.

• Ajit Ananthram

  SSC Veteran

  Points: 211

  More actions

  March 26, 2013 at 4:11 pm

  #1600975

  alishahryar (3/26/2013)

  ---

  I came to add exactly the same point - SCOPE_IDENTITY() would guarantee that the just-inserted rows' newly-generated IDENTITY value would be unique.

  As mentioned earlier, the scope_identity function has been known to generate incorrect values at times (when queries make use of parallel execution plans). Refer to this link for more information --> http://support.microsoft.com/kb/2019779

  Ajit Ananthram
  Blog - http://ajitananthram.wordpress.com

• alishahryar

  SSC Journeyman

  Points: 79

  More actions

  March 26, 2013 at 4:19 pm

  #1600976

  OK, that's good information regarding the bug affecting @@IDENTITY and SCOPE_IDENTITY(). According to the article you provided, the bug is fixed in 2008 R2 SP 1 and above. Thanks for pointing this out.

• JJ B

  SSCertifiable

  Points: 5145

  More actions

  April 9, 2013 at 5:05 pm

  #1605063

  NBSteve: Wow. That was so kind of you to take the time to write that out. Not wordy in the slightest. Instead, it was *clear.* I greatly appreciated it!

  (Also thank you to the original author. I enjoy articles that explains a person's process/thinking. And also thank you to Paul for posting your interesting solution.)