harriyott.com

@@IDENTITY and SCOPE_IDENTITY() in SQL Server

I've just been told about @@IDENTITY, and how it won't always do as expected. When running a stored procedure that does something like the following:

INSERT INTO TEST VALUES( 1, 2, 3, 4, 5)
SELECT @ReturnValue = @@IDENTITY

the @@IDENTITY returns the value of the new row's identity field. If there's a trigger on the table that inserts data into another table, then @@IDENTITY will contain the ID from that table, not the one expected. This is because @@IDENTITY is global. SCOPE_IDENTITY() is local, so the following will return the same value whether there is an insert trigger or not:

INSERT INTO TEST VALUES( 1, 2, 3, 4, 5)
SELECT @ReturnValue = SCOPE_IDENTITY()

Therefore, one should use SCOPE_IDENTITY() instead of @@IDENTITY.
28 June 2005