| Author: bharat kumar 29 Jul 2009 | Member Level: Gold Points : 2 |
T-SQL: The difference between @@identity, scope_identity(), and ident_current()
First of all, when discussing the three methods, you need to understand two concepts: session and scope. 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.
Now that these two concepts are defined, here are the differences between the three identity retrieval methods:
• @@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
To illustrate, imagine the following scenario: You execute an insert command on a Customer table, which fires an insert trigger that inserts a default Order for that customer in the Order table. Immediately after, before you execute another command, another session does the same thing.
• @@identity will return the identity value of the Order table, because the trigger on the Customer table executes after the insert into the Customer table, so the Order identity is the last identity generated in your session but any scope.
• scope_identity() will return the identify value of the row you inserted into the Customer table, because that's the last identity value generated at this level of scope in your session
• ident_current( Customer ) and ident_current( Order ) will return the identity of the rows inserted by the other session.
For most scenarios most of the time, what you will want is the value returned by scope_identity().
|