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

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 :)

Answer 2

@@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 3

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 4

If you wanted the trigger use you could get another trigger added on is the only reason I can come up with. Even then it's dangerous as another trigger could be added and again you would get the wrong identity. I suspect the developer doesn't know what he is doing. But honestly the best thing to do is to ask him why he made the change. You could change it back, but the developer needs to know not to do that again unless he needs the trigger identity as you may not catch it the next time.

Answer 5

Maybe you should ask the developer their rationale behind making the change.