This post originated from an RSS feed registered with .NET Buzz by Roy Osherove. Original Post: Great SQL tip: @@IDENTITY==bad, scope_identity()==good Feed Title: ISerializable Feed URL: http://www.asp.net/err404.htm?aspxerrorpath=/rosherove/Rss.aspx Feed Description: Roy Osherove's persistent thoughts Latest .NET Buzz Posts Latest .NET Buzz Posts by Roy Osherove Latest Posts From ISerializable

And another tip discovered by Sirsha. This time it's from SQLTeam. A very good SQL Server tip that I really must cling to!

The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking for.

“ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.

@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.

Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.

scope_identity() is much nicer. It gives you what you're expecting.”