Follow @RoyOsherove on Twitter

Great SQL tip: @@IDENTITY==bad, scope_identity()==good

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.”

The predictable history of a programmer (reloaded)

Web Designer/Graphic Artist/Receptionist