Reason for using @@identity rather than scope_identity

Question

On a SQL Server 2005 database, one of our remote developers just checked in a change to a stored procedure that changed a "select scope_identity" to "select @@identity". Do you know of any reasons why you'd use @@identity over scope_identity?

Answer 1

@@IDENTITY will return the last identity value issued by the current session. SCOPE_IDENTITY() returns the last identity value in the current session and same scope. They are usually the same, but assume a trigger is called which inserted something somewhere just before the current statement. @@IDENTITY will return the identity value by the INSERT statement of the trigger, not the insert statement of the block. It's usually a mistake unless he knows what he's doing.

Answer 2

Here is a link that may help differentiate them

looks like:

• IDENTITY - last identity on the connection
• SCOPE_IDENTITY - last identity you explicitly created (excludes triggers)
• IDENT_CURRENT(’tablename’) - Last Identity in table regardless of scope or connection.

Answer 3

I can't think of any, unless there was a trigger then inserted a row (or somesuch) and I really really wanted the id of that trigger-inserted row rather than the row I physically changed.

In other words, no, not really.

• DISCLAIMER: Not a T-SQL expert :)