Doug Seven has a good article about 10 things not to do in SQL Server. All are very good points, and you should read the whole thing if you do any development with SQL Server. But the one that caught my eye was number nine - the difference between @@IDENTITY and SCOPE_IDENTITY.

Basically, @@IDENTITY will return the last identity column inserted during your session, regardless of scope, where as SCOPE_IDENTITY is by session, but limits it to the current scope. Broken down, basically SCOPE_IDENTITY isn't affected by triggers, and @@IDENTITY is. So if you have a trigger that inserts a record based on something you do in your stored procedure, and it has an identity field, @@IDENTITY will return the wrong value for you (if you intend to get your own identity). SCOPE_IDENTITY will return the correct value.

I've been doing SQL Server development for quite a few years now, and I never knew that!