在开发里跟 SQL Server 打交道有个操作几乎天天都跑不掉往一张带 IDENTITY 标识列的表里插一条数据紧跟着就要拿到这条新纪录的自增主键值拿去写订单明细、返回给前端、或者做日志关联。早期我还在用 ADO.NET 的时候习惯性写完 INSERT 就顺手SELECT IDENTITY后来被坑过一次才彻底搞清楚这玩意儿的水有多深。这篇文章我就把“最后插入的标识值”这个话题摊开讲从原理到实战、从单行插入到并发场景把该注意的坑一次性说清楚。这章内容适合刚接触 SQL Server 的初学者也适合写过几年 SQL 但没深究过IDENTITY和SCOPE_IDENTITY()区别的朋友。看完你至少能明确回答三个问题标识值到底是什么插入后怎么稳定拿到它以及为什么某些写法在高并发和触发器场景下就是会翻车。1. 标识列基础IDENTITY 到底是什么1.1 IDENTITY 属性的核心机制标识列说白了就是一个由数据库自动生成、自动递增的整数列。建表时这样写CREATE TABLE dbo.Orders ( OrderID INT IDENTITY(1, 1) PRIMARY KEY, OrderNo VARCHAR(32) NOT NULL, CustomerID INT NOT NULL );IDENTITY(1, 1)里的第一个参数叫种子值也就是从几开始第二个参数叫增量也就是每次长多少。上例中第一行数据的 OrderID 是 1第二行是 2依次类推。如果不指定种子和增量默认就是IDENTITY(1, 1)。这个自动生成的动作是在插入语句执行时由存储引擎完成的你不需要也不能直接往这个列里塞值。它比在应用层用 GUID 或者自己写计数器的好处是数据库层面保证唯一性、索引友好、写入顺序大致有序对聚集索引的页拆分压力也小很多。1.2 标识列适用与不适用场景标识列适合做代理主键也就是我们常说的“无意义主键”。它本身不承载业务含义只是用来唯一标识一行、给外键引用用。订单号、身份证号、员工工号这类有业务含义的数据都不应该用标识列代替否则后续业务规则一变比如订单号需要带日期前缀你就会被迫处理一大坨存量数据。同时要注意标识列一旦指定就没法轻易改成业务字段。如果你想在订单表里把订单号搞成20250101 自增号正确做法是保留 OrderID 作为代理主键另外建 OrderNo 业务编号列两者互不干扰。标识值断裂、跳号都不是问题这是它的正常行为千万别为了“让号连续”去手动重置 IDENTITY那才是给自己挖坑。1.3 标识列的数据类型选择标识列常用类型有 INT、BIGINT、SMALLINT、TINYINT也可以用 NUMERIC 和 DECIMAL必须小数位是 0。选型的时候要有点预判INT 最大到 21 亿多很多业务表看着够用但如果是日志、流水、消息这种高吞吐表几年就可能打满到时候改数据类型代价非常大。我建议核心业务表直接上 BIGINT宁可现在稍微浪费几个字节也别赌未来。SYSTEM_VERSIONED 临时表、分区表这些高级特性里也常常依赖标识列做定位类型够大能省很多事。另外如果表已经建好了才发现类型不够SQL Server 允许通过 ALTER TABLE 修改列类型但表很大时会锁表重建得安排在维护窗口里。2. 三种获取方式的原理对比为什么有人会踩坑2.1 IDENTITY 是“会话级”的不是“语句级”的很多老开发者习惯性在 INSERT 之后马上执行SELECT IDENTITY这在小系统里看起来没问题但它的语义是当前会话最后一次由任何 INSERT 语句生成的标识值。问题就出在“任何”两个字上。假如 Orders 表上有一个触发器你插入订单时触发器往 AuditLog 表也插入了一条记录而 AuditLog 表同样有标识列那么IDENTITY返回的是触发器里那条 AuditLog 的标识值而不是你 Orders 表的 OrderID。这类 bug 非常隐蔽因为数据量小、触发器逻辑简单的时候开发环境根本测不出问题上线后才偶尔出现“订单关联了错误的日志ID”之类怪象。2.2 SCOPE_IDENTITY() 为什么值得优先选择SCOPE_IDENTITY()的语义是当前会话、当前作用域内最后一次产生的标识值。作用域可以粗略理解为一个存储过程、一个触发器、一个批处理或一个函数。普通 INSERT 语句是在你自己的作用域里执行的触发器里的插入在系统创建的子作用域里执行SCOPE_IDENTITY()自然就不会被INSERT触发器内部生成的标识值干扰。INSERT INTO dbo.Orders (OrderNo, CustomerID) VALUES (ORD20250101001, 1001); SELECT SCOPE_IDENTITY() AS NewOrderID;这就是多数场景下的黄金组合INSERT 后面紧跟SELECT SCOPE_IDENTITY()拿到的一定是当前这条 INSERT 生成的值。注意它也是会话级的别的会话插的数据不会影响你所以并发压力再大、别人插再多数据这个返回值都是你自己的那条。在中文社区里我见过不少“最好别用 IDENTITY用 SCOPE_IDENTITY()”的结论但很少有人说清楚背后的作用域原理。希望上面的解释能帮你真正理解而不只是记住结论。2.3 IDENT_CURRENT(table_name) 的正确姿势IDENT_CURRENT(dbo.Orders)返回的是指定表里最近一次生成标识值注意它的作用域是全局的不管哪个会话生成的都算。比如 A 会话插入了一条获取到 100B 会话再插入一条获取到 101这时 A 会话去查IDENT_CURRENT(dbo.Orders)拿到的是 101 而不是 100。这个函数适合用在这样的场景你只是想了解某个表当前标识值已经走到哪了比如清理数据后重新规划、做监控告警、或者估算插入进度但不适合用它来获取刚插入那行的标识值。它最大的风险就是并发环境下的“值漂移”你要是拿它当业务主键回写很可能会把 B 会话生成的 ID 安在 A 会话的数据上。2.4 三种函数对比速查表函数/属性作用域是否受触发器影响并发安全度推荐用途IDENTITY当前会话全局受影响中不推荐用于取本行IDSCOPE_IDENTITY()当前会话 当前作用域不受影响高单行插入后取新ID的首选IDENT_CURRENT(表名)服务器级别指定表不受影响低监控当前标识值进度不用于业务回写这张表我建议存下来面试和实际开发都能用上。理解了这个区别你也就明白了为什么网上所有经验贴都在强调“用 SCOPE_IDENTITY() 代替 IDENTITY”。3. 实战细说SCOPE_IDENTITY() 怎么用才稳3.1 单行插入的标准写法标准写法不复杂关键是养成习惯把取值和插入放进同一个批次或同一段逻辑里避免中间隔了其他语句产生干扰。一个典型示例DECLARE NewOrderID INT; INSERT INTO dbo.Orders (OrderNo, CustomerID) VALUES (ORD20250101002, 1002); SET NewOrderID SCOPE_IDENTITY(); PRINT 生成的新订单ID CAST(NewOrderID AS VARCHAR(20));有的人会写成先执行 INSERT然后在应用层再发一条SELECT SCOPE_IDENTITY()这会有两个问题一是多一次数据库往返损耗性能二是如果连接不是同一个拿到的很可能是空值或者别人会话的值。用 Dapper、EF Core 的时候正确做法是让 INSERT 和 SELECT 在同一个批处理里发给数据库或者利用 OUTPUT 子句把值直接带出来。比如 Dapper 可以这样var orderId connection.ExecuteScalarint( INSERT INTO dbo.Orders (OrderNo, CustomerID) VALUES (OrderNo, CustomerID); SELECT SCOPE_IDENTITY();, new { OrderNo ORD20250101003, CustomerID 1003 });EF Core 里如果设置了标识列作为主键SaveChanges 后实体的主键属性会自动被填充内部也是通过类似机制实现的所以不用手工再查一次。注意一点如果你用了触发器或诡异的数据访问封装EF Core 自动填充的值可能不准此时建议显式使用 OUTPUT 子句。3.2 事务中回滚后标识值会怎样事务包裹一个 INSERT然后回滚那 SCOPE_IDENTITY() 返回什么答案可能会让一些人意外它会返回这次插入生成的标识值尽管这行数据已经被回滚掉了。因为标识值的生成发生在插入尝试时回滚只是撤销了数据写入但递增计数器不会回退。这个特性是 SQL Server 设计上保证的目的就是避免并发下的一堆回滚导致标识值冲突。你不要去尝试手动修复这个“空洞”跳号本来就是标识列的正常状态。实际业务中如果你想判断插入是否真的成功不要依赖标识值是否为 NULL而应该检查受影响的函数例如ROWCOUNT或者直接把 INSERT 放进事务并监听是否有异常触发回滚。3.3 存储过程中通过 OUTPUT 参数返回新 ID写存储过程时更优雅的做法是把新 ID 作为 OUTPUT 参数返回而不是让客户端去执行第二条查询CREATE PROCEDURE dbo.InsertOrder OrderNo VARCHAR(32), CustomerID INT, NewOrderID INT OUTPUT AS BEGIN SET NOCOUNT ON; -- 避免额外影响行数干扰客户端 INSERT INTO dbo.Orders (OrderNo, CustomerID) VALUES (OrderNo, CustomerID); SET NewOrderID SCOPE_IDENTITY(); END;调用的时候从应用程序层把NewOrderID当输出参数接住即可。注意SET NOCOUNT ON是存储过程里的好习惯否则 DONE_IN_PROC 消息会影响某些客户端框架对返回结果集的判断特别是在老版本的驱动里尤其明显。4. OUTPUT 子句能拿到一整批标识值的进阶方案4.1 OUTPUT 基本用法SCOPE_IDENTITY() 有一个天生的短板它只能拿到最后那一个标识值。如果你用一条 INSERT 语句插入了多行比如INSERT INTO ... SELECT ...想拿到这一批生成的所有 ID它就无能为力了。这时候需要 OUTPUT 子句出手。DECLARE InsertedIDs TABLE (NewOrderID INT); INSERT INTO dbo.Orders (OrderNo, CustomerID) OUTPUT INSERTED.OrderID INTO InsertedIDs VALUES (ORD20250101004, 1004), (ORD20250101005, 1005); SELECT * FROM InsertedIDs;执行完这条语句表变量InsertedIDs里就是两行新增数据的 OrderID。OUTPUT 子句之所以强大是因为它直接从插入逻辑流里返回实际写入的值不依赖会话、不依赖作用域也不会有并发干扰天然比SCOPE_IDENTITY()更可靠。4.2 批量插入时如何全部捕获实际业务里最常见的批量场景是程序一次性提交了一批订单明细或者数据导入服务往主表插几千行。用 OUTPUT INSERTED 可以一次性收集所有新 ID再回填到业务对象里。比如这样DECLARE NewIDMapping TABLE ( TempID INT NOT NULL, RealID INT NOT NULL ); -- 用 MERGE 或循环逐行插入同时记录映射关系最稳妥的批量套路是“先用临时键做关联再通过 OUTPUT 拿到真实标识值”。因为标识列是数据库生成的你很难在应用层预测。一个常见实现是给源数据每行加一个业务流水号插入后通过 OUTPUT 把 INSERTED 的主键列和源行的流水号一起捞出来再更新回源表。DECLARE Source TABLE ( TempID INT IDENTITY(1, 1) PRIMARY KEY, OrderNo VARCHAR(32), CustomerID INT ); INSERT INTO Source (OrderNo, CustomerID) VALUES (ORD20250101006, 1006), (ORD20250101007, 1007), (ORD20250101008, 1008); DECLARE Mapping TABLE (TempID INT, RealOrderID INT); INSERT INTO dbo.Orders (OrderNo, CustomerID) OUTPUT inserted.OrderID, s.TempID INTO Mapping (RealOrderID, TempID) SELECT o.OrderNo, o.CustomerID FROM Source s JOIN dbo.Orders o ON 1 0; -- 这里仅演示实际不会这么JOIN上面的写法只是为了说明思路实际执行时不会用ON 10这种写法。更干净的方案是利用 MERGE 的 OUTPUT 子句把$action和源行标识一起拿出来我下面会单独讲。4.3 使用 MERGE 时的 OUTPUT 操作MERGE 是 SQL Server 里一个多功能语句能把源数据和目标表做匹配存在则更新、不存在则插入。它同样支持 OUTPUT 子句而且还能告诉你每条数据是 INSERT、UPDATE 还是 DELETE 操作这在同步数据场景里非常有价值。MERGE INTO dbo.Orders AS T USING (VALUES (ORD20250101009, 1009), (ORD20250101010, 1010)) AS S(OrderNo, CustomerID) ON 1 0 -- 永远不匹配等价于全部插入 WHEN NOT MATCHED THEN INSERT (OrderNo, CustomerID) VALUES (S.OrderNo, S.CustomerID) OUTPUT inserted.OrderID, S.OrderNo;这里ON 1 0是为了演示大家最好理解的全部走插入逻辑实际同步业务中你会根据真实关联条件来写。OUTPUT 子句能从 MERGE 里把插入后生成的主键原样返回配合源表业务键就能很准确地建立新旧数据映射关系比逐行查 SCOPE_IDENTITY() 快了不止一个数量级。有个容易忽略的点MERGE 的 OUTPUT 在同时执行多类操作时INSERTED和DELETED列需要按操作类型区分。比如 UPDATE 操作里INSERTED.OrderID是更新后的值DELETED.OrderID是更新前的值。如果列名存在歧义建议给源表和目标表都起别名并且显式注明。4.4 OUTPUT 与触发器共存的注意事项SQL Server 对触发器表INSTEAD OF 触发器和带 OUTPUT 的 INSERT 有兼容问题。当表上有 INSTEAD OF 触发器时OUTPUT 可能无法按照预期返回实际插入到表里的数据因为真正的数据写入发生在触发器内部而不是原始 INSERT 语句里。这时候你得在触发器里把目标表的标识值放入临时表再在外部读取。另外如果表上有 AFTER 触发器OUTPUT 通常是正常的但要注意 OUTPUT 子句生成的结果集在网络传输里有大小限制。一次性插入几十万行时OUTPUT 会产生同样多的行应用层处理不当会有内存压力。大数据量导入建议分段提交比如每批次 5000 到 10000 行既避免事务日志暴涨也保护客户端内存。5. 并发场景下的安全性与性能分析5.1 高并发下三种函数的表现我经常被问到并发量高了以后用 SCOPE_IDENTITY() 会不会取到别的会话生成的 ID答案是基本不会。SCOPE_IDENTITY() 的作用域是“当前会话 当前作用域”SQL Server 会为每个会话维护各自的标识值上下文不会互相覆盖。并发再高你最多在锁等待阶段排队一旦 INSERT 完成拿到的就是自己的值。IDENTITY在并发下同样不会跨会话串因为会话是隔离的。它的风险主要来自“作用域内的其他插入”比如触发器。IDENT_CURRENT()则完全可能拿到别的会话的生成值所以永远不要拿它做业务回写。在高并发下数据库的竞争点通常不在取值逻辑上而在目标表自身。大量插入同一张表时锁升级、页闩锁竞争都可能拖慢整体速度。如果你的插入性能出现瓶颈优先检查等待类型、索引页拆分情况而不是怀疑 SCOPE_IDENTITY()。5.2 连接池下会话模型的变化在应用层使用连接池时一个物理连接会被多个业务线程复用但这不意味着 SCOPE_IDENTITY() 会串。连接是串行复用的同一时刻只会有一个命令在一个连接上执行。你发出 INSERT 和 SELECT SCOPE_IDENTITY() 的批处理如果语句在同一批次里、没有中断那么它们一定在同一个会话上下文中执行返回值就是你那条数据。风险点在于有些人把 INSERT 发送出去后没有立刻取值而是先做了一些耗时的业务逻辑然后连接被池子收回又被另一个请求占用。此时你再执行 SELECT SCOPE_IDENTITY()虽然还是原会话但会话里可能已经执行过别的插入了返回的自然就不是你之前那条。解决办法就是前面强调的INSERT 和取值必须放同一个批次或同一个事务里中间不要留“空窗”。5.3 标识值分配与性能足迹很多人不知道IDENTITY 值的分配在 SQL Server 里不是完全独立的高性能序列而是基于内存中的当前值和增量进行的每批分配结果会写入日志以保证持久化。插入频繁的进程中标识值分配本身开销很小但当发生大量并发插入时内存中标识值缓存的更新可能产生少量闩锁竞争。SQL Server 2022 之前标识值的批量缓存由实例管理。数据库发生重启或故障转移时可能出现标识值跳一大段的情况这是正常现象不是数据丢失。SQL Server 2022 引入了 IDENTITY_CACHE 相关选项可以按需控制缓存量但这里不展开后续可以单独开一章聊聊这个话题。对绝大多数应用而言不理会这种跳号完全没问题。6. 高频翻车场景复盘触发器、回滚与标识值断裂6.1 触发器引发的经典事故有一次我给一张业务表加了个审计触发器插入一行后自动往审计表写一条记录。上线第二天同事反馈某个订单的 CreatedByID 永远等于审计表的自增 ID怎么都对不上。排查到最后就是代码里用了IDENTITY而不是SCOPE_IDENTITY()。这里把复现场景写出来CREATE TABLE dbo.AuditLog ( AuditID INT IDENTITY(1, 1) PRIMARY KEY, ActionName VARCHAR(32), RefOrderID INT ); CREATE TRIGGER dbo.trg_Orders_Insert_Audit ON dbo.Orders AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.AuditLog (ActionName, RefOrderID) SELECT INSERT, OrderID FROM INSERTED; END;那么执行INSERT INTO dbo.Orders (OrderNo, CustomerID) VALUES (ORD20250101011, 1011); SELECT IDENTITY AS BadValue, SCOPE_IDENTITY() AS GoodValue;你会发现IDENTITY返回的是 AuditLog 表的 AuditID而 SCOPE_IDENTITY() 才是 Orders 表的 OrderID。这个例子非常典型我建议你亲自动手建这两张表测一遍踩过坑印象最深。6.2 事务回滚后标识值为什么不回退还有一个常见疑问我开了事务插入失败了回滚了但下一个插入的标识值仍然跳了一位为什么答案前面提过标识值计数器在尝试插入时已经递增回滚不会让它回退。这在设计上是刻意为之因为如果回滚就让标识值回退并发情况下会非常难保证唯一性。想象一下两个事务同时插入一个回滚一个提交如果回滚把计数器拽回去就可能产生重复值。所以别费力气去清理标识值空洞。某些系统给人感觉“ID中间缺了一段”八成就是之前有过失败插入或回滚。只要主键不重复业务能正常联表就不要管它。6.3 显式插入标识值带来的连锁反应如果你确实需要手工往标识列里塞值可以用SET IDENTITY_INSERT dbo.Orders ON。但开启后你插入一个比当前标识值还大的数计数器会跟着跳到这个值。比如当前标识值 100你显式插入 200下一次正常自动生成的值会变成 201。很多人忽略这一点结果数据迁移后新插入的数据从很大一个数开始吓了一跳。还有一个细节IDENTITY_INSERT 一个表只能在一个会话里开启而且必须在同一个会话里执行插入和关闭。忘记关闭的话后续在该表上的自动插入会一直报错。用完立刻SET IDENTITY_INSERT dbo.Orders OFF是铁律。6.4 快速排查清单症状可能原因排查重点取到的ID是审计表的ID用了IDENTITY且有触发器改用SCOPE_IDENTITY()检查触发器返回值与最近插入不一致INSERT与取值不在同一会话/批次检查连接是否被池复用、批处理拆分批量插入只拿到最后一个ID用了SCOPE_IDENTITY()改用OUTPUT子句新插入的ID突然跳过几千实例重启/故障转移/事务回滚确认非故障无需处理插入报错且无法生成IDIDENTITY_INSERT未关闭显式关闭或重开会话这套速查表基本上覆盖了日常 90% 以上的问题定位路径。真遇到时建议先排查“会话和作用域”这两个维度问题往往很快就水落石出。7. 从 SQL Server 2008 到 2022 的行为差异与兼容性7.1 老版本函数的稳定性SQL Server 2008 R2、2012、2014 等老版本上SCOPE_IDENTITY() 和 OUTPUT 的行为与新版基本一致这是官方长期保证的兼容性。早期版本里如果涉及到触发器需要注意 SQL Server 2000 时代遗留的 IDENTITY 习惯代码迁移到新版本时最好一并升级为 SCOPE_IDENTITY()。OUTPUT子句在 2005 开始就有已经非常成熟。所以如果你在维护老系统完全可以直接把SELECT IDENTITY替换成SELECT SCOPE_IDENTITY()不会有兼容问题。需要谨慎的是 2012 新增的 SEQUENCE 对象和 2019 之后的某些特性它们和旧版驱动配合时可能出现明显的语法不支持错误。7.2 新版增强点概览SQL Server 2022 引入了IDENTITY_CACHE选项可以在建表或 ALTER TABLE 时控制标识值缓存大小例如CREATE TABLE dbo.Orders ( OrderID INT IDENTITY(1, 1) PRIMARY KEY ) WITH (IDENTITY_CACHE 50);这个选项的主要价值是控制故障转移时标识值跳变的幅度。缓存设得越小故障时可能丢失的预分配标识值就越少但换来的是更高的分配开销。绝大多数系统用默认值就好只有那种对 ID 连续性有强迫症要求的场景才需要调整。另外Azure SQL Database 和 SQL Server 2022 都强化了智能查询处理对带 OUTPUT 的批量插入也会有更稳定的计划选择但底层行为没变。这章不堆功能清单只提醒一点任何新版本的上线都务必先把现有涉及标识值的代码回归测试一遍尤其是触发器 OUTPUT 的组合场景。8. 值得长期坚持的几条编码习惯最后分享几个我从实际项目里提炼出的习惯不一定多高深但真的能帮你躲开大部分莫名其妙的 bug。第一条所有 INSERT 后取值不要写IDENTITY统一用SCOPE_IDENTITY()。存量代码在动到的地方顺手改掉因为触发器一加老代码随时会炸。第二条INSERT 和取值必须在同一个批处理或同一个存储过程里中间不穿插任何其他语句。这能避免连接池复用带来的会话上下文漂移问题。第三条批量插入需要回填 ID 时不要循环调 SCOPE_IDENTITY()直接用 OUTPUT INTO 临时表或表变量。循环不仅慢还放大了日志和锁的覆盖范围数据量一大就是性能灾难。第四条凡是涉及显式插入标识列的代码必须写好注释并把 IDENTITY_INSERT 的开关放在紧邻的位置不跨函数、不跨非常规调用最好用 try/finally 包裹。第五条排查问题时先分清“当前会话”和“当前表”。SCOPE_IDENTITY() 跟会话走IDENT_CURRENT() 跟表走这两个维度最容易弄混也是最常见的翻车根源。这些年我见过太多“昨天还好好的今天突然关联错数据”的案例最后定位到全是标识值取值方式的问题。把这套机制彻底想明白你就再也不会在这一类问题上浪费半个通宵了。