回滚含默认约束的事务为何导致SQL Server IDENTITY列出现10000间隔？

问题场景 #

操作步骤：

• 开启事务（BEGIN TRANSACTION）
• 添加约束（ADD CONSTRAINT）
• 插入数据（INSERT）
• 回滚事务（ROLLBACK）

执行代码：

SELECT * FROM Champ   -- 最后ID为30014

BEGIN TRANSACTION

ALTER TABLE Champ 
    ADD CONSTRAINT DF_Champ_NumWins DEFAULT 0 FOR NumWins

INSERT INTO Champ(Name, DateOfBirth)
VALUES('TestDummy', '1911/12/11')

ROLLBACK

INSERT INTO Champ(Name, DateOfBirth)
VALUES('TestDummy', '1911/12/11')

SELECT * FROM Champ    -- 最后ID变为40015

表初始定义：

CREATE TABLE [dbo].[Champ]
(
    [ChampID] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](250) NOT NULL,
    [DateOfBirth] [datetime] NOT NULL,
    [CreateTSO] [datetimeoffset](7) NOT NULL,
    [NumWins] [bigint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Champ] 
    ADD CONSTRAINT [DF_Champ_CreateTSO] DEFAULT (SYSDATETIMEOFFSET()) FOR [CreateTSO]

现象：执行后IDENTITY列直接出现10000的间隔；若事务内不执行插入操作则无间隔，若不添加约束则仅间隔1。

原因分析 #

这是SQL Server 2016及后续版本的IDENTITY缓存机制导致的：

1. SQL Server为提升IDENTITY列的生成性能，会预分配一批IDENTITY值作为缓存——默认情况下，bigint类型的缓存大小是10000，int类型是1000。
2. 当你在事务中执行ALTER TABLE添加默认约束时，这个操作会触发表的架构修改锁（Sch-M），同时会重置IDENTITY的缓存分配。
3. 事务回滚时，预分配的这批缓存值会被直接丢弃，不会被复用。
4. 事务内同时完成架构修改和插入操作后回滚，相当于预分配的10000个值被跳过，因此后续插入时IDENTITY值直接从30014 + 10001 = 40015开始，出现10000的间隔。

其他场景的差异原因：

• 不添加约束时，仅执行插入后回滚，只会消耗缓存中的1个值（未触发缓存重置），因此仅间隔1；
• 事务内不执行插入时，缓存未被使用，自然不会产生间隔。

内容的提问来源于stack exchange，提问作者user1675016