Reason for using @@identity rather than scope_iden

问题:

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?

回答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.

回答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.

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

回答4:

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

回答5:

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.