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

• The @@identity function returns the last identity created in the same session.
• The scope_identity() function returns the last identity created in the same session and the same scope.
• The ident_current(name) returns the last identity created for a specific table or view in any session.
• The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

So, normally you would use the scope_identity() function.

Good question.

• @@IDENTITY: returns the last identity value generated on your SQL connection (SPID). 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(): returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

• IDENT_CURRENT(): returns the last identity value for a specific table. Don't use this to get the identity value from an INSERT, it's subject to race conditions (i.e. multiple connections inserting rows on the same table).

• IDENTITY(): used when declaring a column in a table as an identity column.

For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx.

To summarize: if you are inserting rows, and you want to know the value of the identity column for the row you just inserted, always use SCOPE_IDENTITY().

If you understand the difference between scope and session then it will be very easy to understand these methods.

A very nice blog post by Adam Anderson describes this difference:

Session means the current connection that's executing the command.

Scope means the immediate context of a command. Every stored procedure call executes in its own scope, and nested calls execute in a nested scope within the calling procedure's scope. Likewise, a SQL command executed from an application or SSMS executes in its own scope, and if that command fires any triggers, each trigger executes within its own nested scope.

Thus the differences between the three identity retrieval methods are as follows:

@@identity returns the last identity value generated in this session but any scope.

scope_identity() returns the last identity value generated in this session and this scope.

ident_current() returns the last identity value generated for a particular table in any session and any scope.