在SqlServer上做开发的人八成遇到过这种场景一张订单表主键是IDENTITY自增列数据一条没少可编号却从 1001 直接跳到 1003中间那个号跟人间蒸发一样。业务员拿着纸质单据来质问DBA查了半天说没删过数据重启过一次服务器编号就对不上了。老大一拍桌子“把自增列跳号给我禁了。”然后你就开始查资料、翻文档越查越发现这问题比想象中麻烦得多。这个需求听起来简单做起来却到处都是坑。SqlServer的自增列IDENTITY从设计上就没保证过“连续”它只保证两件事唯一、递增。想让编号不跳号要么在应用层动手脚要么在事务层做补偿没有哪个开关能一键关闭跳号。这篇文章我会把IDENTITY的运作机制、跳号的根因、以及三种真正能落地的“不跳号”方案一次性讲透适合被业务方追着问的开发人员也适合做数据库维护的DBA参考。1. 自增列为什么会“跳号”先搞懂IDENTITY的运行机制1.1 IDENTITY 到底是什么在SqlServer里加自增列只需要一行定义CREATE TABLE dbo.Orders ( OrderId INT IDENTITY(1,1) PRIMARY KEY, OrderNo VARCHAR(20) NOT NULL, OrderData NVARCHAR(200) NOT NULL );IDENTITY(1,1) 表示种子是1步长是1。你只负责INSERT数据SqlServer会自动给OrderId赋值。很多人以为自增列和Excel的自动填充差不多实际上它是一个由数据库引擎维护的内部计数器每次插入行时由系统分配一个值这个分配动作发生在INSERT语句执行的那一刻而不是事务提交的那一刻。这个时间差是理解跳号问题的关键。你要记住一句话VALUES被分配出去的时候事务还没有提交但计数器已经往前走了一步。1.2 跳号的三个主要根因回滚、重启、删除根因一事务回滚。这是最常见的跳号方式。你写了一个事务里面插入了几行数据然后因为某个约束失败或者应用代码抛异常事务整体回滚。插入的行被还原了但IDENTITY计数器不会跟着回退。原因很好理解SqlServer必须保证已经分配出去的编号不会再分配给其他事务否则并发插入时会出现主键冲突。为了换取这个安全它宁可把编号浪费掉。根因二异常重启和故障转移。SqlServer为了性能不会每次插入都更新磁盘上的自增值它会在内存里缓存一批编号默认以1000个为单位批量取得标识值。如果实例正常关闭缓存状态会妥善保存但如果是进程崩溃、机房断电、强制failover内存里那一批还没有真正写入行的编号就永久丢失了。重启后实例从磁盘读到的当前值可能落后于内存中已分配的编号所以SqlServer会预留一段区间防止将来编号重复。表现就是你什么都没干重启之后下一个编号凭空跳了1000甚至更多而这个区间里没有任何数据行。根因三删除与显式插入。DELETE掉的行当然不会释放自增值显式插入也会推进计数器。如果你打开了IDENTITY_INSERT ON手动插入了一个很大的编号比如99999那么下一个自增值就是从100000开始中间空了一大段。还有人在测试环境反复做INSERT、DELETE、再INSERT跳号会更明显。1.3 缓存机制是怎么放大跳号幅度的关于缓存再展开说一下。默认情况下SqlServer为自增列分配标识值时会成批地取批量大小是1000。这意味着你插入了1条数据实际内存里已经预占了1000个编号用掉1个剩下999个继续用。正常流程里你感知不到因为接下来999条记录都会紧跟着上一条表现是连续的。一旦实例异常终止剩下的缓存编号全部作废下一次启动后下一个ID就会比崩溃前记录的当前值多出1000。这就解释了为什么有些生产环境平时跳号只跳过1个、2个某次重启后突然跳了几千个。如果你维护的系统出现过跳号量恰好是1000的倍数基本可以断定是异常重启或failover造成的。2. 哪些场景最容易让你发现“跳号”2.1 事务失败回滚引发的“小跳号”我在排障时遇到最多的是这个业务逻辑里写了事务比如创建订单时同时插入主表、明细表中间某一步报错事务回滚界面提示“操作失败”。用户很自然地重试一次这次成功了。结果订单编号从1002变成了1004中间的1003本来是第一次重试时应该用的但它随着失败的INSERT一起消失了。这类跳号幅度很小一次就跳1个或几个如果你没有手动记录上一次的编号甚至很难察觉。2.2 异常宕机引发的“大跳号”有一次客户凌晨机房断电第二天业务系统恢复后业务员发现物流单号从 500000 直接跳到了 501000。数据库检查出来没有任何数据丢失就是自增列突然空出一千个编号。这种跳号幅度大而且没有规律可循非常容易触发业务投诉。2.3 数据清理和ID重用测试开发环境里经常有人写DELETE FROM Orders清空表再把数据重新灌进去。测试做完忘了处理直接上线后面发现编号从100开始而历史单据可能已经到了1000接口调用方和业务方全都乱了。还有一种情况手痒执行了DBCC CHECKIDENT(Orders, RESEED, 0)想重新开始编号结果主键与其他表发生引用冲突关联查询直接错乱。3. 想“禁止跳号”先分清边界与克制3.1 底层没有“禁止跳号”的开关你可以在SqlServer找到很多配置开关但没有一个叫“禁止自增列跳号”的选项。原因不是微软偷懒而是IDENTITY的核心语义就不包含连续性。官方文档里对IDENTITY属性的描述是该属性在插入行时自动生成唯一编号但并不保证编号之间没有间隔。如果把“唯一”和“递增”当作物理ID的职责把“连续”当作业务单据号的职责你会想通很多问题。实际上和IDENTITY同属序列体系的SEQUENCE对象也一样调用NEXT VALUE FOR后即使事务回滚也不会把已经取出的值还回去。这是SQL标准里序列的设计方式所有主流关系型数据库都这样。3.2 你能控制的范围业务层的连续编号既然底层做不到我们就换个角度思考业务方真正关心的不是数据库物理ID连续而是打印出来的单据号连续不能出现断号。这就是业务层的连续编号需求。在这个层面主动权其实在我们手里。控制范围有三个编号在何时分配。如果编号在事务提交成功后才生成并落库那么失败事务不会产生任何编号也就没有空洞。编号如何管理。使用单独的计数器表、业务序号列或者顺序号工具替代IDENTITY作为业务编号。编号如何展示。物理表里可以保留有空洞的ID但给业务展示时用行号或者序号列补成连续。接下来的实操方案全部围绕这三个控制点展开。4. 实操三种落地方法实现“不跳号”先声明一点这三种方案都不再使用IDENTITY列作为业务单号而是把IDENTITY降级为物理主键或者干脆不用IDENTITY。下面按推荐程度从低到高讲。4.1 方法一手工编号 串行化事务适合强一致、低并发这是最接近“禁止跳号”字面意思的做法。原理是在一次事务里通过查询已有数据的最大连续值加1后作为新单号写入数据表。如果事务失败回滚下次执行会再次查找最大连续值由于之前的插入已回滚最大连续值没变所以下一次会重新使用那个编号不会产生空洞。示例表结构CREATE TABLE dbo.SalesOrder ( OrderId INT IDENTITY(1,1) PRIMARY KEY, -- 物理键仅用于内部关联 OrderNumber INT NOT NULL, -- 业务连续编号 CustomerId INT NOT NULL, Amount DECIMAL(18,2) NOT NULL, CreateTime DATETIME2 DEFAULT SYSDATETIME(), CONSTRAINT UQ_OrderNumber UNIQUE (OrderNumber) );注意OrderNumber加了唯一约束这是防止并发下重复编号的最后一道防线。然后写一个存储过程来分配编号CREATE PROCEDURE dbo.CreateSalesOrder CustomerId INT, Amount DECIMAL(18,2) AS BEGIN SET NOCOUNT ON; DECLARE NewNumber INT; BEGIN TRAN; -- 关键UPDLOCK HOLDLOCK 锁定整个范围让并发请求排队 SELECT NewNumber ISNULL(MAX(OrderNumber), 0) 1 FROM dbo.SalesOrder WITH (UPDLOCK, HOLDLOCK); INSERT INTO dbo.SalesOrder (OrderNumber, CustomerId, Amount) VALUES (NewNumber, CustomerId, Amount); COMMIT; SELECT NewNumber AS OrderNumber; END GO这段代码的重点是WITH (UPDLOCK, HOLDLOCK)。UPDLOCK让查询获取更新锁HOLDLOCK把锁持有到事务结束两个一起用等于事务内把整个表的数据区间锁住了。第二个事务要执行同样操作时必须等第一个事务提交或回滚之后才能读取MAX值。这样保证了同一时间只有一个事务在计算新编号同时新编号在没有插入成功的情况下不会被其他事务抢走。这个方案的代价非常明显写并发能力被压到极低。所有INSERT都要排队拿表级锁吞吐量上不去。适合订单量不大、并发低、但业务对编号连续性要求极高的场景比如内部的审批单、合同登记、小规模票据流水。有一点要提醒这个方案里OrderId仍然有跳跃但业务方看的是OrderNumber物理ID的跳跃不影响业务。如果你想彻底省掉物理ID也可以直接用OrderNumber做主键但考虑到其他表的外键引用和索引效率我建议保留IDENTITY物理键。4.2 方法二IDENTITY物理键 连续序号列推荐既然物理ID连续与否不重要而业务方只要一张连续的表单编号那么最省事的做法是表里既有IDENTITY主键也有一个业务序号列插入成功后按提交顺序给业务序号赋值。这个赋值动作不需要手工算MAX而是用视图或者查询生成。我先看建表CREATE TABLE dbo.Invoice ( InvoiceId INT IDENTITY(1,1) PRIMARY KEY, InvoiceNo INT NOT NULL, ClientName NVARCHAR(100) NOT NULL, Amount DECIMAL(18,2) NOT NULL, CreateTime DATETIME2 DEFAULT SYSDATETIME() );每次INSERT时InvoiceNo先放一个占位值比如0提交成功后再更新成一个连续值。这段逻辑放在一个可重复执行的存储过程里CREATE PROCEDURE dbo.Invoice_Create ClientName NVARCHAR(100), Amount DECIMAL(18,2) AS BEGIN SET NOCOUNT ON; DECLARE NewId INT; DECLARE NewNo INT; BEGIN TRAN; INSERT INTO dbo.Invoice (InvoiceNo, ClientName, Amount) VALUES (0, ClientName, Amount); SELECT NewId SCOPE_IDENTITY(); SELECT NewNo ISNULL(MAX(InvoiceNo), 0) 1 FROM dbo.Invoice WITH (UPDLOCK, HOLDLOCK); UPDATE dbo.Invoice SET InvoiceNo NewNo WHERE InvoiceId NewId; COMMIT; SELECT NewId AS InvoiceId, NewNo AS InvoiceNo; END GO思路是先用自增列得到物理ID行落库后锁住表计算出下一个连续序号再回填到当前行。这个方法的好处是不管前面发生过多少事务回滚还是服务器重启过多少次InvoiceNo都会按照“最后成功提交的行”顺序排列不会跳号。缺点依然是并发能力受限因为计算MAX时把表锁住了。但相比方法一至少物理ID是由系统维护的不需要手工处理主键问题代码写起来不容易出错。如果你连UPDATE回填那一步都省了不存储连续编号只在查询时用ROW_NUMBER()生成效果也差不多SELECT InvoiceId, ROW_NUMBER() OVER (ORDER BY InvoiceId) AS ContinuousNo, ClientName, Amount FROM dbo.Invoice;这种方法最稳妥。展示给用户看的单据号永远是连续行号底层物理表里怎么跳号都无所谓也不需要额外的锁。缺点是不能直接把ContinuousNo当业务主键来关联其他表只适合报表展示和页面输出。我不止一次劝过业务部门单独从列表里看单号连续就够了没必要把单号存成持久化字段。但有些金融、物流系统确实要求单号在数据库提交那一刻就是连续且固定的这时候就用带UPDATE回填的过程。4.3 方法三接受跳号用“补号程序”事后填洞还有一类特殊场景系统已经上线了表里已经有大量ID是断的但业务方非要历史数据也全部连续。这时候既不想动表结构又不想重写业务代码就只能在空闲时段做一次“填洞”操作。具体做法是找出所有缺失的ID把后面行的ID往前挪同时更新所有引用该ID的外键表。这活儿听着简单实施起来牵一发动全身。我建议你先停车评估一下成本再动手。先查找空洞的简单脚本;WITH CTE_NumberGap AS ( SELECT OrderId, LAG(OrderId) OVER (ORDER BY OrderId) AS PrevOrderId FROM dbo.Orders ) SELECT PrevOrderId 1 AS GapStart, OrderId - 1 AS GapEnd FROM CTE_NumberGap WHERE OrderId - PrevOrderId 1;这个脚本会列出所有编号间断的区间。填洞则需要重建主键、重建外键、更新所有的子表还要处理唯一索引冲突操作窗口内要停写。越是核心表风险越大。我的经验是除非上级明确要求且愿意承担停机风险否则别做物理填洞宁可写一个只读视图呈现给业务方一个连续的“展示号”。历史数据永远做不到无风险地全局重排。5. 实操中的锁、并发与性能取舍5.1 UPDLOCK HOLDLOCK到底锁住了什么很多人不理解为什么要加这两个锁提示。我们拆开看UPDLOCK把查询时默认的共享锁改成更新锁。更新锁和更新锁之间互相排斥所以两个事务不能同时读取同样的数据做更新准备。HOLDLOCK把锁的持有时间延长到事务结束。否则SELECT执行完锁就释放了事务提交前另一个请求就能插入相同编号造成重复。两个锁提示组合起来等效于把编号查询和INSERT变成临界区一次只有一个事务能进入。这叫牺牲并发保一致适合低并发高价值业务。5.2 手工MAX(ID)1为什么是错误示范我看到过的生产事故里最典型的是这么写的-- 错误示范千万别学 INSERT INTO Orders (OrderNumber, CustomerId) SELECT ISNULL(MAX(OrderNumber), 0) 1, CustomerId FROM Orders;这样做的坑在于SqlServer默认隔离级别是READ COMMITTED两个事务同时执行时可能都读到同一个MAX值然后生成两个相同的OrderNumber。由于没有唯一约束脏数据就这么写进去了。即使有唯一约束也只是从“数据重复”变成“插入报错”让业务直接失败。在辅助函数、UDF或者存储过程里看到这种写法我建议一律改成事务内锁表或者干脆改用IDENTITY物理键。5.3 三种方案的对照方案连续性保证并发能力实现复杂度适用场景手工编号串行事务强低中低频强一致审批单IDENTITY序号回填强低中单据编号需入库保存展示层ROW_NUMBER展示连续物理不连续高低报表、页面列表没有完美的方案核心就是看“连续编号”到底要不要持久化。要持久化就上锁能接受计算就上视图。6. 常见问题与排查速查6.1 为什么我什么都没干ID跳了1000大概率是异常重启导致的缓存丢失。确认方法查Windows事件日志里是否有SQL Server进程崩溃记录查sqlserver日志是否有“恢复”相关条目。如果跳号量是1000的整数倍基本可以锁定缓存问题。这不是配置错误也不需要处理设置上没办法完全避免。6.2 DBCC CHECKIDENT 能把自增列重置回连续吗能改当前值但没意义。执行DBCC CHECKIDENT(dbo.Orders, RESEED, 1000);会把下一个自增ID设为1001。但如果表里已经存在1001下一次INSERT就会主键冲突。如果表和已有数据有外键引用重置会破坏引用关系。我的建议是只在清空表数据后重置种子其他情况下不要动它。6.3 事务回滚后自增值真的不会回退吗真的不会。你可以自己做个实验BEGIN TRAN; INSERT INTO dbo.Orders (OrderData) VALUES (AAA); ROLLBACK; INSERT INTO dbo.Orders (OrderData) VALUES (BBB); SELECT * FROM dbo.Orders;你会发现第一条记录的ID被消耗了。这属于设计行为不是bug也关闭不了。想让业务单号回退只有回到方法一、方法二那种“业务序号自己管理”的路线。我将常见的排查点整理成了一张速查表方便你直接对着处理症状可能原因检查手段解决方案跳号1个或几个事务回滚应用日志中有报错回滚记录业务编号改为提交后生成跳号1000倍数异常宕机/故障转移事件管理器查看非正常关闭正常流程不处理或改申请逻辑规避DELETE后ID不连续删除数据确认DELETE记录展示行号代替物理ID重置种子后主键冲突DBCC CHECKIDENT误操作查询当前最大ID回滚操作或改回原值并发下重复单号MAX1手工编号检查是否存在唯一约束改为事务锁表或IDENTITY6.4 SqlServer的辅助热点问题顺带解答几个和自增列无关但经常被搜到的问题。字符串转数字用CAST(123 AS INT)、CONVERT(INT, 123)或者TRY_CAST推荐后者转换失败返回NULL不会报错中断业务。多行合并成一行用STRING_AGG函数SqlServer 2017及以上版本支持老版本配合FOR XML PATH实现。OFFSET后再TOP 20查到的是什么OFFSET跳过指定行后再TOP取前N行结果就是分页查询的第二页核心逻辑是ORDER BY必须稳定否则同一次分页不同页之间会出现重复或遗漏。这些基础问题经常和自增列问题一起出现在新手问答里说明很多人是在处理报表、分页和单据编号时碰到这类麻烦的。7. 最后一个实用提醒如果非要用IDENTITY做业务编号又不想看到跳号有一个妥协方案把业务单据号显示规则改成“前缀日期自增序号”的字符串自增序号只在当天连续。跨天重置可以通过任务每天早上把种子重置为1DBCC CHECKIDENT(dbo.DailyNo, RESEED, 0);不过这方案在断电、回滚时依然会跳号只是业务感知没那么强。我个人在实际操作中的体会是物理ID是给数据库用的业务单号是给人用的把它们混为一谈是跳号问题的根源。做了十年SqlServer运维我现在接到“禁止自增列跳号”需求时从来不会去试图改SqlServer行为而是先问清楚业务系统要这个连续编号做什么用。如果是打印单据、对账、追溯就上序号列或者行号如果只是数据库关联直接告诉对方IDENTITY跳号是正常设计。先定位需求再选方案省下的时间够你把一套报表系统都搭完了。