Alternatives to @@IDENTITY in SQL Server 2000

By Sean Baird on 19 September 2000 | 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 IDENTITY information. The result of each of these three functions is dependent on three factors:

The session scope (which connection produced the IDENTITY value?)
The table scope (which table produced the IDENTITY value?)
The statement scope (where is the statement that produced the IDENTITY value?)

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

SELECT @@IDENTITY
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.

SELECT IDENT_CURRENT('tablename')
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.

SELECT SCOPE_IDENTITY()
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:

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))
GO
CREATE TRIGGER tI_Yak ON YakName FOR INSERT
AS
BEGIN
    INSERT YakTracker (TranType, YakName)
    SELECT 'I',YakName FROM inserted
END
GO

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')
SELECT @@IDENTITY

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

-SQLGuru