Home » Blog » Identity Crisis

Identity Crisis

2011-08-27 - General, SQL Server Internals

I wanted to write about this one for a while now but never got around to it. Today I came across a Connect issue that reminded me of this topic again.

If you have bee around SQL Server for a while you propably have seen this function:
[sql]SELECT @@IDENTITY AS LastIdentityValue[/sql] It allows you to retrieve the last identity value generated during the last insert that was executed in your connection.
This can be very useful when you have to insert a new row in a table with an identity column primary key and you also have to insert rows that reference the new row into a child table.

The problem with @@IDENTITY is, that it actually does exactly what it says it does: It returns the most recent identity value that was generated for your connection. That means, if you insert into a table with an identity column and that insert fires a trigger in which an insert into another table with an identity column gets executed, @@IDENTITY will return the identity value generated for that second insert statement in that is part of the trigger. This value is most likely not the one that you want.

Now you might think, that you do not have triggers and therefore you do not have a problem here. Think about what happens if your boss tomorrow tells the new employee to write some auditing code. That colleague of yours might use triggers that insert into auditing tables with an identity column. Suddenly your code starts producing wrong values. These are the kinds of hard to find bugs that will cost you some long nights debugging.

So you probably want to not use @@IDENTITY in your code — ever. But what alternative is available?

There is the IDENT_CURRENT() function:
[sql]SELECT IDENT_CURRENT('dbo.MyTable') AS LastIdentityValue[/sql] It takes a table name as parameter and returns the last identity value generated for that table.

But that is probably not the value you are after either: IDENT_CURRENT() does not take the connection into account. So if you are in a highly concurrent environment, you will get values returned that are far off the value that was generated for your connection.

But there is hope:
[sql]SELECT SCOPE_IDENTITY() AS LastIdentityValue[/sql] SCOPE_IDENTITY() returns the last identity value generated for your connection and within the current scope. That means that identity values generated for anything else like other connections or even triggers in the same connections do not affect the value returned by SCOPE_IDENTITY().

The following code example demonstrates the different values returned by the three functions.
[sql] IF OBJECT_ID('dbo.IdentityTest') IS NOT NULL DROP TABLE dbo.IdentityTest;
IF OBJECT_ID('dbo.IdentityTestForTrigger') IS NOT NULL DROP TABLE dbo.IdentityTestForTrigger;
GO
CREATE TABLE dbo.IdentityTest(id INT IDENTITY(1,1));
CREATE TABLE dbo.IdentityTestForTrigger(id INT IDENTITY(13,1));
GO
CREATE TRIGGER dbo.IdentityTestTrigger ON dbo.IdentityTest AFTER INSERT
AS
BEGIN
INSERT INTO dbo.IdentityTestForTrigger DEFAULT VALUES;
END;
GO

INSERT INTO dbo.IdentityTest DEFAULT VALUES;

WAITFOR DELAY '00:00:10';

SELECT @@IDENTITY AS [@@IDENTITY],IDENT_CURRENT('dbo.IdentityTest') AS [IDENT_CURRENT('dbo.IdentityTest')],SCOPE_IDENTITY() AS [SCOPE_IDENTITY()];

[/sql] Within ten seconds of executing this code, run the following code in a different connection:
[sql] INSERT INTO dbo.IdentityTest DEFAULT VALUES;
INSERT INTO dbo.IdentityTest DEFAULT VALUES;
INSERT INTO dbo.IdentityTest DEFAULT VALUES;
[/sql]

Only SCOPE_IDENITYT() returns the desired value 1. @@IDENTITY returns 13 and IDENT_CURRENT() returns 4 — both are values that we are not looking for.

But before you now start to jubilate as the problem seems solved, hold on a minute: SCOPE_IDENITYT() does not work!

The details of the problem you can find in KB 2019779. There is a connect item referenced in this article. This item is marked as fixed, however there is no information given which version(s) of SQL Server received the fix.

So I can not recommend to use SCOPE_IDENITYT() either.

Instead you can use the OUTPUT clause:
[sql] DECLARE @IdentValues TABLE(v INT);

INSERT INTO dbo.IdentityTest
OUTPUT INSERTED.id INTO @IdentValues(v)
DEFAULT VALUES;

SELECT v AS IdentityValues FROM @IdentValues;
[/sql] This code snippet assumes you ran the previous example to setup the tables and the trigger.

This method works without any (known) problems. It also allows you to get to all identity values generated during this insert, not only the last one. However, if you need just the last one, replace the select statement in the example with this one:
[sql] SELECT MAX(v) AS LastIdentityValue FROM @IdentValues;
[/sql]

There is one additional benefit of this method: If you go ahead next year and change all your identity columns to use the new Denali feature SEQUENCE, this method will continue to work unchanged:
[sql] IF OBJECT_ID('dbo.SequenceTable') IS NOT NULL DROP TABLE dbo.SequenceTable;
IF OBJECT_ID('dbo.TestSequence') IS NOT NULL DROP SEQUENCE dbo.TestSequence;
GO
CREATE SEQUENCE dbo.TestSequence AS INT;
CREATE TABLE dbo.SequenceTable (id INT DEFAULT NEXT VALUE FOR dbo.TestSequence);
GO
DECLARE @IdentValues TABLE(v INT);

INSERT INTO dbo.SequenceTable
OUTPUT INSERTED.id INTO @IdentValues(v)
DEFAULT VALUES;

SELECT v AS IdentityValues FROM @IdentValues;
[/sql]