For the last time, NO, you can't trust IDENT_CURRENT()

I had a discussion yesterday with Kendal Van Dyke (@SQLDBA) about IDENT_CURRENT(). Basically, Kendal had this code, which he had tested and trusted on its own, and wanted to know if he could rely on IDENT_CURRENT() being accurate in a high-scale, concurrent environment:

BEGIN TRANSACTION;
INSERT dbo.TableName(ColumnName) VALUES('Value');
SELECT IDENT_CURRENT('dbo.TableName');
COMMIT TRANSACTION;

The reason he had to do this is because he needs to return the generated IDENTITY value to the client. The typical ways we do this are:

• SCOPE_IDENTITY()
• OUTPUT clause
• @@IDENTITY
• IDENT_CURRENT()

Some of these are better than others, but that's been done to death, and I'm not going to get into it here. In Kendal's case, IDENT_CURRENT was his last and only resort, because:

• TableName had an INSTEAD OF INSERT trigger, making both SCOPE_IDENTITY() and the OUTPUT clause useless from the caller, because:
  • SCOPE_IDENTITY() returns NULL, since the insert actually happened in a different scope
  • the OUTPUT clause generates error Msg 334 because of the trigger
• He eliminated @@IDENTITY; consider that the INSTEAD OF INSERT trigger could now (or might later be changed to) insert into other tables that have their own IDENTITY columns, which would mess up the returned value. This would also thwart SCOPE_IDENTITY(), if it were possible.
• And finally, he couldn't use the OUTPUT clause (or a resultset from a second query of the inserted pseudo-table after the eventual insert) within the trigger, because this capability requires a global setting, and has been deprecated since SQL Server 2005. Understandably, Kendal's code needs to be forward compatible and, when possible, not rely completely on certain database or server settings.

So, back to Kendal's reality. His code seems safe enough – it's in a transaction, after all; what could go wrong? Well, let's take a look at a few important sentences from the IDENT_CURRENT documentation (emphasis mine, because these warnings are there for good reason):

Transactions are barely mentioned in the body of the document (only in the context of failure, not concurrency), and no transactions are used in any of the samples. So, let's test out what Kendal was doing, and see if we can get it to fail when multiple sessions are running concurrently. I'm going to create a log table to keep track of the values generated by each session – both the identity value that was actually generated (using an after trigger), and the value claimed to be generated according to IDENT_CURRENT().

First, the tables and triggers:

-- the destination table:

CREATE TABLE dbo.TableName
(
  ID INT IDENTITY(1,1), 
  seq INT
);

-- the log table:

CREATE TABLE dbo.IdentityLog
(
  SPID INT, 
  seq INT, 
  src VARCHAR(20), -- trigger or ident_current 
  id INT
);
GO

-- the trigger, adding my logging:

CREATE TRIGGER dbo.InsteadOf_TableName
ON dbo.TableName
INSTEAD OF INSERT
AS
BEGIN
  INSERT dbo.TableName(seq) SELECT seq FROM inserted;

  -- this is just for our logging purposes here:
  INSERT dbo.IdentityLog(SPID,seq,src,id)
    SELECT @@SPID, seq, 'trigger', SCOPE_IDENTITY() 
    FROM inserted;
END
GO

Now, open a handful of query windows, and paste this code, executing them as close together as possible to ensure the most overlap:

SET NOCOUNT ON;

DECLARE @seq INT = 0;

WHILE @seq <= 100000
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.TableName(seq) SELECT @seq;
  INSERT dbo.IdentityLog(SPID,seq,src,id)
    SELECT @@SPID,@seq,'ident_current',IDENT_CURRENT('dbo.TableName');

  COMMIT TRANSACTION;
  SET @seq += 1;
END

Once all of the query windows have completed, run this query to see a few random rows where IDENT_CURRENT returned the wrong value, and a count of how many rows in total were affected by this misreported number:

SELECT TOP (10)
  id_cur.SPID,  
  [ident_current] = id_cur.id, 
  [actual id] = tr.id, 
  total_bad_results = COUNT(*) OVER()
FROM dbo.IdentityLog AS id_cur
INNER JOIN dbo.IdentityLog AS tr
   ON id_cur.SPID = tr.SPID 
   AND id_cur.seq = tr.seq 
   AND id_cur.id <> tr.id
WHERE id_cur.src = 'ident_current' 
   AND tr.src     = 'trigger'
ORDER BY NEWID();

Here are my 10 rows for one test:

I found it surprising that almost a third of the rows were off. Your results will certainly vary, and may depend on the speed of your drives, recovery model, log file settings, or other factors. On two different machines, I had vastly different failure rates - by a factor of 10 (a slower machine only had in the neighborhood of 10,000 failures, or roughly 3%).

Immediately it's clear that a transaction isn't enough to prevent IDENT_CURRENT from pulling the IDENTITY values generated by other sessions. How about a SERIALIZABLE transaction? First, clear the two tables:

TRUNCATE TABLE dbo.TableName;
TRUNCATE TABLE dbo.IdentityLog;

Then, add this code to the beginning of the script in multiple query windows, and run them again as concurrently as possible:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

This time, when I run the query against the IdentityLog table, it shows that SERIALIZABLE may have helped a little bit, but it hasn't solved the problem:

And while wrong is wrong, it looks from my sample results that the IDENT_CURRENT value is usually only off by one or two. However, this query should yield that it can be *way* off. In my test runs, this result was as high as 236:

SELECT MAX(ABS(id_cur.id - tr.id))
FROM dbo.IdentityLog AS id_cur
INNER JOIN dbo.IdentityLog AS tr
  ON id_cur.SPID = tr.SPID 
  AND id_cur.seq = tr.seq 
  AND id_cur.id <> tr.id
WHERE id_cur.src = 'ident_current' 
  AND tr.src     = 'trigger';

Through this evidence we can conclude that IDENT_CURRENT is not transaction-safe. It seems reminiscent of a similar but almost opposite problem, where metadata functions like OBJECT_NAME() get blocked - even when the isolation level is READ UNCOMMITTED - because they don't obey surrounding isolation semantics. (See Connect Item #432497 for more details.)

On the surface, and without knowing a lot more about the architecture and application(s), I don't have a really good suggestion for Kendal; I just know that IDENT_CURRENT is *not* the answer. :-) Just don't use it. For anything. Ever. By the time you read the value, it could already be wrong.