SQL Server 2012 自动增长列，值跳跃问题

此文为译文（英文水平有限），原文链接：SQL Server 2012 Auto Identity Column Value Jump Issue

 

介绍

从 SQL Server 2012 版本开始， 当SQL Server 实例重启之后，表格的自动增长列的值会发生跳跃，而具体的跳跃值的大小是根据增长列的数据类型而定的。如果数据类型是 整型(int)，那么跳跃值为 1000；如果数据类型为 长整型(bigint)，那么跳跃值为 10000。从我们的项目来看，这种跳跃问题是不能被接受的，尤其是展示在客户端的时候。这个奇怪的问题只在 SQL Server 2012 及更高的版本中存在，SQL Server 2012之前版本不存在此问题。

 

背景

几天前，我们QA组的同事提出： 我们表格的自增列的值莫名奇妙的跳跃了 10000。也就是说，我们之前表格自增列的最后一个值为 2200，而现在新增一条记录，自增列的值却直接变成了 12200。在我们的业务逻辑中像这样的情况是不允许展现在客户端的，因此我们要解决此难题。

 

代码使用

刚开始我们都很奇怪，这是怎么发生的？我们通常不会手动向自增列插入任何值（向自增列手动插入值是可以的），自增列的值是由数据库自行维护的。我们核心团队的一位成员开始研究这个问题并找到了答案。现在，我想详细讲解下这个问题，以及我同事找到的解决方案。

 

如何重现此bug

你需要安装SQL Server 2012 然后创建一个测试数据库。之后再创建一个带有自增列的表格：

create table MyTestTable(Id int Identity(1,1), Name varchar(255));

现在插入两条数据：

insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson'); 

查看结果：

SELECT Id, Name FROM MyTestTable;

 

此时结果和我们预期的一样。 现在重启你的 SQL Server Service。重启SQL服务有多种方法，我们这里通过 SQL Server 管理器来重启：

 

重启之后，我们向刚才的表格再插入2条数据：

insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

查看结果：

SELECT Id, Name FROM MyTestTable;

 

现在你看到重启SQL Server 2012 之后的结果，它的自增列的值从1002开始了。 也就是跳跃了 1000。之前说过，如果我们自增列的数据类型是 长整型（bigint）的话，它的跳跃值就将会是 10000。

 

它真的是个BUG吗？

微软声明这是一个功能而并非bug, 在很多场景下是很有用处的。 但是在我们的案例中，我们并不需要这样的一个功能，因为这个自增数据是要展示给客户的，客户如果看到这样跳跃性的数据，他们会感到很奇怪。并且跳跃值是根据你重启SQL Server的次数决定的。如果此数据不向客户展示，或许还可以接受。因此此功能通常只适合在内部使用。

 

解决方案

如果我们对微软提供的这个 “功能” 不感兴趣，我们可以通过两种途径来关闭它。

1. 使用序列 (Sequence)

2. 为SQL Server 注册启动参数 -t272

 

使用序列

首先，我们需要移除表格的自增列。然后创建一个不带缓存功能的序列，根据此序列插入数值。 下面是示例代码：

CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom'); 
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson'); 

 

注册启动参数 -t272

打开SQL Server配置管理器。 选择 SQL Server 2012 实例，右键， 选择属性菜单。在弹出的窗口中找到启动参数，然后注册 -t272。 完成之后重启下图中的SQL Server(SQLSERVER2012)， 之后进行bug重现的操作，验证问题是否已解决。

 

额外说明

如果在你的数据库中有很多自增列的表，并且这些表都存在数值跳跃问题，那么采用第2种方案更好一些。因为它非常简单，并且作用域是服务器级别的。采用第2种解决方案将会影响此服务实例上的所有数据库。