Return to Alternatives to @@IDENTITY in SQL Server 2000
Alternatives to @@IDENTITY in SQL Server 2000
Written by Sean Baird on 19 September 2000
Mark writes: "I was asked a question today by a developer having problems getting the right identity value from @@identity when a table has a trigger which has an additional insert - Post the insert statement the select @@identity returns the wrong value (which is behaviour I would expect).
Is there a trick to get round this, apart from not using triggers and/or not using identity columns - which is what I suggested ..."
Prior to SQL Server 2000, the answer was "no". However, SQL Server 2000 adds two cool new functions to help you get around this problem. Read on...
SQL Server 2000 has three functions that return
IDENTITY information. The result of each of these three functions is dependent on three factors:
(SQL Statements that are contained in the same batch, stored procedure, or trigger are considered to be in the same scope. So, if I call an
- The session scope (which connection produced the
- The table scope (which table produced the
- The statement scope (where is the statement that produced the
INSERT that fires a trigger, I have two different scopes: scope 1 is inside the batch that called the
INSERT, and scope 2 is inside the trigger.)
This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last
IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
This new function returns the last
IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
This new function returns the last
IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
Okay, I think these are best explained with an example, and since Mark has provided a good one, we'll use it.
Let's create some tables:
This is a basic scenario that duplicates the problem Mark described. Whenever I insert a record(s) into YakName, the trigger will insert a record(s) into YakTracker (we need audit information on the Yaks, apparently).
CREATE TABLE YakName (ID int IDENTITY(1,1), YakName varchar(30))
CREATE TABLE YakTracker (ID int IDENTITY(1000,1), TranType char(1), YakName varchar(30))
CREATE TRIGGER tI_Yak ON YakName FOR INSERT
INSERT YakTracker (TranType, YakName)
SELECT 'I',YakName FROM inserted
Now, Mark's developer is trying something like this:
INSERT YakName VALUES ('Graz')
Although what we really want from this batch of statements is the last
IDENTITY value for the YakName table, we get the value 1000--the last
IDENTITY value for the YakTracker table. Why is this? The second insert statement in the trigger also inserts into a table with an identity. Remember,
@@IDENTITY works across all tables (YakName, YakTracker) and all scopes (batch scope, trigger scope), so it picked up the change we made to a different table (YakTracker) in a different scope (the trigger)!
Prior to SQL Server 2000, we would have been stuck. At this point, we'd have to eliminate the
IDENTITY column on the YakTracker table to get these statements working the way we want them to.
But let's say we've shelled out the cash for the latest copy of SQL Server 2000. Let's look at how the new functions would behave:
INSERT YakName VALUES ('Billy Joe Bob')
SELECT SCOPE_IDENTITY() --returns the value 2
SELECT IDENT_CURRENT('YakName') --returns the value 2 (maybe)
SELECT IDENT_CURRENT('YakTracker') --returns the value 1001 (maybe)
SCOPE_IDENTITY() works for all tables in the scope for which it was called, which in this case is the original batch. So, we get the last value for the YakName table, which is what we wanted.
Note that I included some samples for
IDENT_CURRENT as well. Unless someone else on another connection is also inserting values into the YakName table, you will get the results shown in the example. (Remember that the
IDENT_CURRENT function disregards which connection produced the last
IDENTITY value for the specified table.)
Admittedly, these are some pretty handy functions. Of course, I saw these and wanted more... Wouldn't it be nice to have a rowset function that returned all of the
IDENTITY values created because of a multi-row insert? Oh well, I guess we have to wait through another couple of versions to see that feature. :)