SQL Server自增列跳号总结

从SQL Server 2012开始，Microsoft就引入了一个新的功能/特性IDENTITY CACHE，引入这个功能的目录是为了提高包含自增列(IDENTITY)相关表的INSERT性能。但是随之而来的，就是可能会出现自增列(标识列)的跳号问题，如下所示：

CREATE TABLE TEST(ID INT IDENTITY(1,1), NAME VARCHAR(16));

INSERT INTO TEST(NAME)
SELECT 'Kerry1' UNION ALL
SELECT 'Kerry2';

SELECT * FROM TEST;

然后，我们通过Windows任务管理器，杀掉SQL Server服务的进程，模拟SQL Server Abort或服务器意外重新启动的情况。或者你使用下面命令关闭数据库实例

SHUTDOWN WITH NOWAIT

我们重启数据库实例后，继续插入数据，你会发现字段ID跳号了，如下截图所示：

INSERT INTO TEST(NAME)
SELECT 'Kerry3';

自增列跳号的差值(GAP)

从上面的测试实验，我们看到自增列ID的跳号的差值(GAP)为1000，那么有一个疑问，所有的自增列跳号的差值（GAP）都是1000吗？ 答案是No，这个跳号的差值(GAP)跟自增列的数据类型有关，如果自增列字段类型为INT类型的话，差值(GAP)为1000，如果自增列为BIGINT或Numeric数据类型的话，跳号(GAP)为10000，这个有兴趣的话，可以自行测试一下。这里就不展开叙述了。

另外，生产环境自增列跳号的值有可能小于1000或10000，不一定完全是1000或10000，你可以模拟这种情况，如下所示，在测试环境循环插入数据，然后模拟异常重启。

DECLARE @insert_rows INT =0
DECLARE @row_count INT=100000

WHILE @insert_rows < @row_count
BEGIN
INSERT INTO TEST(NAME)
SELECT 'KADFADFIAT'

 SET @insert_rows =@insert_rows+1
END;

因为官方文档缺少相关资料，对其内部原理所知甚少，所以很难准确描述为什么跳号会小于1000或10000，我们只需知道有这种现象。我曾绞尽脑汁设想一些可能性，但是都缺乏证据支持。

什么时候出现自增列跳号？

什么场景会出现自增列跳号呢？ SQL Server 由于出于性能原因而缓存自增列的值，在数据库故障或服务器重新启动期间，一些分配的值可能丢失。 这可能导致在插入时自增列值（标识值)之间有间隔。具体来说，分为下面几种情况：

• 1： SQL Server奔溃或服务器意外重启（例如，服务器宕机、蓝屏、SQL Server实例Crash、SHUTDOWN WITH NOWAIT）
• 2： AlwaysON的故障转移时出现自增列跳号。如果正常的重启SQL Server服务/实例是不会导致自增列跳号的。至于原因和内部原理，没有相关的官方解释，但是参考ORACLE SEQUENCE跳号总结^[1]，大体原理估计也差不多。

如何禁止自增列跳号

1：跟踪标记禁用IDENTITY CACHE

在早期的SQL Server版本，例如，SQL Server 2012、2014等版本，由于一些业务逻辑的要求，你要杜绝/禁止自增列跳号的话，必须通过跟踪标记272来禁用IDENTITY CACHE，避免在服务器意外重新启动(Abort/宕机)或故障转移到辅助服务器的情况下，禁用自增列预分配以避免标识列的值出现跳号的情况，这个是在全局/实服务器级别设置。如下所示：

2：使用序列替换自增列

如果不允许有间隔，应用程序应使用自己的机制来生成键值。使用没有CACHE的序列替换自增列。将序列生成器与 NOCACHE 选项结合使用可以限制从未提交的事务的间隔。

3：在数据库级别禁用IDENTITY CACHE

从SQL Server 2017开始，Micosoft提供提供了一个新功能，允许你在数据库级别禁用或启用标识缓存(IDENTITY CACHE)。如下所示

USE <YourSQLDB>
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO

切换到指定的数据库，执行下面第二条SQL就能开启IDENTITY CACHE，如果想关闭数据库的IDENTITY CACHE功能的话，就执行下面SQL

USE <YourSQLDba>
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

查询开启还是关闭了IDENTITY CACHE,可以通过下面语句查看

USE <YourSQLDba>
GO
SELECT * FROM sys.database_scoped_configurations WHERE NAME='IDENTITY_CACHE'

参考资料