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

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


Related Articles

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Understanding Identity Columns (9 March 2002)

Identity and Primary Keys (28 February 2001)

Uniqueidentifier vs. IDENTITY (12 September 2000)

Returning @@IDENTITY back to an ASP Page (18 August 2000)

Other Recent Forum Posts

Need help with nested list in SQL (1d)

Can't restore bak file from MS SQL Server 2000 (3d)

Connecting to SQL Server from externally with proxy server (3d)

How to Find if Java is Installed (4d)

How To Insert Title At Start of SQL Results and Between Another SQL Result Sets (4d)

Help with sql query find combinations that are not equal, please (4d)

Transpose Query Help (5d)

Excel blobs and BCP extract commands (6d)

- Advertisement -