SQL Server里怎么搞定那些自动增长的编号，实操经验分享和技巧总结

最基础的就是IDENTITY，但别只会用默认的

刚用SQL Server的人，第一个接触的肯定是IDENTITY属性，建表的时候在字段后面加上IDENTITY(1,1)，意思就是从1开始，每次增加1，这个确实省心，不用管它，插数据的时候自己就涨上去了，但问题也来了，有时候表里的数据被删掉一些，比如你删掉了ID为5、10、15的这三行，你再往里插新数据，ID会接着上次最大的数往后排，不会去填补5、10、15这些空位，一开始我觉得这很别扭，感觉序号不连贯，后来想通了，数据库这么设计是对的，它的首要任务是保证唯一性，而不是美观，你要是硬要去填那些坑，反而会惹出一堆麻烦事，比如可能和别的事务产生冲突，拖慢速度。

这里有个小经验,如果你确定要清空表并且让ID重新从1开始，千万别自己手动去把ID改成1，那样很容易出错，直接用TRUNCATE TABLE 表名这个命令，它不但删得快，而且会自动把IDENTITY的种子重置回一开始设的值（比如1），但是要小心，这个命令不会触发删除触发器，而且如果这个表被别的表通过外键引用了，你就用不了这个命令。

IDENTITY字段的坑：怎么在插入后立刻拿到那个自动生成的号？

这是个非常常见的场景：我往表里插一条新记录，插完之后我马上需要用到这个自动生成的ID号，比如用它作为明细记录的外键，怎么办？有两个方法最常用。

第一个是用SCOPE_IDENTITY()函数，你可以在INSERT语句后面紧接着用SELECT SCOPE_IDENTITY()来获取，这个函数的好处是，它只返回当前会话、当前作用域（比如你写的这个存储过程）里生成的最后一个IDENTITY值，这样就不会被同时运行的其他操作干扰，最安全，我基本上都用这个。

第二个是@@IDENTITY，这个要慎用，它会返回当前会话中任何作用域生成的最后一个IDENTITY值，万一你插数据的这个表上有个触发器，触发器又往另一张有IDENTITY字段的表里插了数据，那@@IDENTITY拿到的就是触发器产生的那个ID，而不是你想要的这个，这就乱套了。

还有个IDENT_CURRENT('表名')，这个是返回指定表里最后生成的IDENTITY值，不管哪个会话生成的，这在某些监控场景有用，但绝对不适合在插入后获取自己的ID，因为可能别人刚插了一条，你就拿到别人的号了。

记住这个经验：无脑用SCOPE_IDENTITY()，准没错。

IDENTITY的进阶玩法：设置增长步长和重置种子

IDENTITY不光能(1,1)，比如我有一个需求，每天的数据的ID要以1000001开头，第二天从2000001开头，这样从ID上就能看出是哪天的数据，我可以把增长步长设大点，比如IDENTITY(1000001, 1000000)，但这要求你每天建一张新表，或者每天手动去重置种子，手动重置种子可以用DBCC CHECKIDENT ('表名', RESEED, 新的种子值)命令，这个操作有点危险，一定要在夜深人静没人的时候做，不然可能造成ID冲突。

另一个大杀器：SEQUENCE对象

从SQL Server 2012开始，多了一个叫SEQUENCE（序列）的东西，它比IDENTITY更灵活，因为它不依赖于某一张表，就是一个独立的数据库对象。

它的好处太多了：

1. 多个表可以共用一个序列，我想让订单表和发货单的ID来自同一个号码池，保证全局唯一且递增，用SEQUENCE就能轻松实现，IDENTITY是表级别的，做不到这点。
2. 可以控制循环，IDENTITY只能一直往上增，直到溢出，SEQUENCE可以设置成循环的，比如从1到100，到了100之后又回到1重新开始（当然要确保唯一性约束等其他手段）。
3. 可以一次获取一批号码，这在需要预生成一批ID用于客户端批量操作时特别有用，能减少和数据库的交互次数，提升性能，用NEXT VALUE FOR语句就能拿。
4. 可以灵活重置，改序列的当前值比改表上的IDENTITY种子要方便和安全一些。

怎么用呢？先创建一个序列：

CREATE SEQUENCE OrderSeq
    START WITH 1
    INCREMENT BY 1;

然后插入数据的时候这样用：

INSERT INTO 订单表 (订单ID, 客户名) VALUES (NEXT VALUE FOR OrderSeq, '张三');

根据我看到的来自“SQL Server Central”社区讨论中的观点，在高并发、需要全局有序序列或者复杂编号规则的场景下，SEQUENCE通常是比IDENTITY更好的选择。

终极技巧：当自动编号不够用时，自己构造编号

有时候业务要求的编号不是简单的数字,而是像“ORD202405270001”这种，带前缀、日期和序号的，IDENTITY和SEQUENCE都只能给数字部分，这时候就得自己动手了。

常见的做法是：

1. 建一个表专门用来存各种类型编号的当前最大值。
2. 在事务中,先锁住这个表的那一行记录，防止别人同时读。
3. 把当前值读出来,加一，然后拼上日期前缀，形成完整的编号。
4. 更新那个最大值记录。
5. 提交事务。

这样做能保证号码绝对不会重,但缺点是会成为性能瓶颈，因为并发高的时候大家要排队等锁，为了缓解这个，可以按日期分区，比如每天都有一个序列，这样锁的粒度就小多了。

还有一种取巧的办法,如果对序号的绝对连续性要求不高，可以用一个计算列，比如ID还是用IDENTITY自动生成，然后另外有一个计算列，公式是 'ORD' + CONVERT(VARCHAR(8), GETDATE(), 112) + RIGHT('0000' + CAST(ID AS VARCHAR(4)), 4)，但这有个问题，如果一天内插入超过9999条，就会出错，而且ID删除后会有空洞。

• 图省心，不共享：用IDENTITY，记得用SCOPE_IDENTITY()拿号。
• 要灵活，要共享，要高并发：用SEQUENCE。
• 编号规则复杂，有业务含义：结合事务和锁，自己管理编号生成。

这些东西都是在实际项目里碰过钉子才总结出来的,希望对你有点用，最关键的是，根据你的具体业务场景，选最合适的那个，别一上来就无脑IDENTITY(1,1)。