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.
Sorry, the comment form is closed at this time.