What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

Question

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current() all get the value of the identity column, but I would love to know the difference.

Part of the controversy I'm having is what do they mean by scope as applied to these functions above?

I would also love a simple example of different scenarios of using them?

Answer 1

@@IDENTITY: This returns the last identity value which is generated on your SQL connection (SPID). But, most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to an INSERT, and the trigger executes another INSERT statement.

SCOPE_IDENTITY(): This returns the last identity value which is generated in the current scope (i.e. stored procedure, trigger, function, and so on).

IDENT_CURRENT(): This returns the last identity value for a specific table. Never use this to get the identity value from an INSERT, as it's the matter of race conditions (i.e. multiple connections inserting rows on the same table).

IDENTITY(): This is used when you are declaring a column in a table as an identity column.

Conclusion: If you are inserting rows, and want to know the value of the identity column for the row that you just inserted, then always use SCOPE_IDENTITY().

For more information, you can refer to http://msdn.microsoft.com/en-us/library/ms187342.aspx.