Difference among @@identity, scope_identity(), and ident_current()
Sep 18, 2009

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().

Leave a Reply

Your email address will not be published. Required fields are marked *


Related Posts

OUTPUT Clause in SQL Server 2005

SQLserver 2005: OUTPUT Clause What: OUTPUT Clause is used to return the rows affected…

Sep 22, 2008

Copyright © Prakash Software Solutions Pvt. Ltd. All Rights Reserved.