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