SCOPE_IDENTITY() vs. @@IDENTITY - Retrieving Identity for Most Recently Added Row in Table

http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

Although @@IDENTITY is one of those common practices in SQL Server 7, which does not have a SCOPE_IDENTITY() function, it is no longer the accepted way to get the identity of the most recently added row in a table in SQL Server 2000 and SQL Server 2005.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.

To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.