This one is one of the basics that all SQL programmers learn at some point, sometimes the hard way.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the row you just added to a particular table. Say you have a trigger that inserts a record into a Logs table whenever your stored procedure or INSERT statement adds a record to the Orders table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the row added to the Logs table instead, which makes for a nasty bug in your data access layer.

To avoid that, and to protect yourself against someone adding a trigger later, use SCOPE_IDENTITY(). It returns the identity of the most recently added row within the current scope, which is the one you actually inserted.

This came up during QA testing, and it was the first thing I thought of. Sure enough, the original developer had used @@IDENTITY to get the newly inserted identity value, which was perfectly correct at the time he wrote it. We had since added trigger functionality, and that is what caused the wrong results. Switching to SCOPE_IDENTITY() fixed it.

If you use triggers anywhere in your database, this is one to remember.