@@Identity Best Practices (MS SQL)

I just ran across this post that was referenced from an article written on MSDN.

For the past 2-3 years I’ve been working under the idea that @@Identity would return the identity of the most recently added row in the table you were just working with. Not so – it will give you the identity of the most recently added row in the entire database! I believe, however, that @@identity is limited to working within the connection to the database. For instance, if you have 2 scripts and they both add a row to the database, they would be using separate connections to the database and thus wouldn’t be confused. If your database uses triggers and your trigger adds a row in another table, @@identity will return the identity of that row, not the one you want.

The alternative offered in the article is to use scope_identity(). It acts the same way, only without this little idiosyncrasy.

Share and Enjoy:
  • del.icio.us
  • digg
  • Furl
  • NewsVine
  • RawSugar
  • Reddit
  • Spurl
  • YahooMyWeb
Written by Tom in: SQL Server |

No Comments

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress | Aeros Theme | TheBuckmaker.com WordPress Themes