http://weblogs.asp.net/arefin/archive/2010/05/04/returning-identity-value-in-sql-server-identity-vs-scope-identity-vs-ident-current.aspx:
" We have some common misconceptions on returning the last inserted identity value
from tables. To return the last inserted identity value we have options to use @@IDENTITY
or SCOPE_IDENTITY or IDENT_CURRENT function depending on the requirement but it
will be a real mess if anybody uses anyone of these functions without knowing exact
purpose. So here I want to share my thoughts on this. @@IDENTITY, SCOPE_IDENTITY
and IDENT_CURRENT are almost similar functions in terms of returning identity value.
They all return values that are inserted into an identity column. Earlier in SQL
Server 7 we used to use @@IDENTITY to return the last inserted identity value because
those days we don’t have functions like SCOPE_IDENTITY or IDENT_CURRENT but now
we have these three functions. So let’s check out which one responsible for what.
IDENT_CURRENT returns the last inserted identity value in a particular table. It
never depends on a connection or the scope of the insert statement. IDENT_CURRENT
function takes a table name as parameter. Here is the syntax to get the last inserted
identity value in a particular table using IDENT_CURRENT function.
SELECT IDENT_CURRENT('Employee') Both the @@IDENTITY and SCOPE_IDENTITY return the last inserted identity value created
in any table in the current session. But there is little difference between these
two i.e. SCOPE_IDENTITY returns value inserted only within the current scope whereas
@@IDENTITY is not limited to any particular scope. Here are the syntaxes to get
the last inserted identity value using these functions
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
Now let’s have a look at the following example. Suppose I have two tables called
Employee and EmployeeLog.
CREATE TABLE Employee
(
EmpId NUMERIC(18, 0) IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(100) NOT NULL,
EmpSal FLOAT NOT NULL,
DateOfJoining DATETIME NOT NULL DEFAULT(GETDATE())
)
CREATE TABLE EmployeeLog
(
EmpId NUMERIC(18, 0) IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(100) NOT NULL,
EmpSal FLOAT NOT NULL,
DateOfJoining DATETIME NOT NULL DEFAULT(GETDATE())
)
I have an insert trigger defined on the table Employee which inserts a new record
in the EmployeeLog whenever a record insert in the Employee table. So Suppose I
insert a new record in the Employee table using following statement:
INSERT INTO Employee (EmpName,EmpSal) VALUES ('Arefin','1') The trigger will be fired automatically and insert a record in EmployeeLog. Here
the scope of the insert statement and the trigger are different. In this situation
if I retrieve last inserted identity value using @@IDENTITY, it will simply return
the identity value from the EmployeeLog because it’s not limited to a particular
scope. Now if I want to get the Employee table’s identity value then I need to use
SCOPE_IDENTITY in this scenario. So the moral is always use SCOPE_IDENTITY to return
the identity value of a recently created record in a sql statement or stored procedure.
It’s safe and ensures bug free code.
