@@Identity

@@Identity
Many TSQL 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.

SELECT SCOPE_IDENTITY() at the end of the query