Weird behavior of DBCC CHECKIDENT with RESEED in a rolled-back transaction

Question

Consider the following T-SQL code (the code is available along with an ADO.NET version in this GitHub repo https://github.com/PaloMraz/SqlServerReseedSampleApp):

DROP TABLE IF EXISTS __Test;
CREATE TABLE __Test (Id INT IDENTITY NOT NULL PRIMARY KEY);
GO

BEGIN TRAN;
SET IDENTITY_INSERT __Test ON;
INSERT INTO __Test (Id) VALUES (100);
SET IDENTITY_INSERT __Test OFF;
DBCC CHECKIDENT('__Test', RESEED, 1);
ROLLBACK TRAN;
GO

SELECT IDENT_CURRENT('__Test'); -- returns 100 instead of 1

The code does the following:

• Creates a __Test table with IDENTITY column.
• Starts a transaction.
  • INSERTs new row overriding the IDENTITY seed value to 100.
  • Reseeds the IDENTITY back to 1.
  • Rolls back the transaction.
• Queries the current identity.

I have expected, that the current identity value will be back at 1, but instead, it is 100. This means that the transaction rolled back the inserted row and the results of the DBCC CHECKIDENT command, but did not roll back the overridden IDENTITY seed!

Is this the correct behavior? Why?

Thank you for your time!

Answer 1

The only real question here is why DBCC CHECKIDENT ... RESEED can be rolled back. With IDENTITY INSERT or not inserting into a table with an IDENTITY column will increment the current identity value without blocking other sessions' ability to generate new IDENTITY values. To do this, the modification of the current IDENTITY value must not be enlisted in the session's transaction.

DBCC CHECKIDENT is effectively a DDL statement, like ALTER TABLE. It requires an exclusive metadata lock on the object, and therefore can be committed or rolled back. EG

BEGIN TRAN;
DBCC CHECKIDENT('__Test', RESEED, 100);

select *
from sys.dm_tran_locks
where request_session_id = @@spid 

ROLLBACK TRAN;

Will show Sch-M locks on the target table.