Alternatives to @@IDENTITY in SQL Server 2000
By Sean Baird
on 19 September 2000
| 6 Comments
| Tags: Identity
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
information. The result of each of these three functions is dependent on three factors:
- The session scope (which connection produced the
- The table scope (which table produced the
- The statement scope (where is the statement that produced the
(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
that fires a trigger, I have two different scopes: scope 1 is inside the batch that called the
, and scope 2 is inside the trigger.)
This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last
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
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
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:
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
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).
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
value for the YakName table, we get the value 1000--the last
value for the YakTracker table. Why is this? The second insert statement in the trigger also inserts into a table with an identity. Remember,
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
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)
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
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
function disregards which connection produced the last
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
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. :)