SQL Server中Identity列是否绝不会生成介于已有值之间的新值？——事件存储场景下的技术问询

好问题！你的核心假设完全正确——在默认配置下，SQL Server的IDENTITY列完全能满足你作为事件存储position的需求，不会出现新插入行的IDENTITY值小于已存在行的情况。下面详细拆解原因、边缘场景和需要规避的操作：

你的理解是对的：SQL Server的IDENTITY值在插入执行时生成，且绝不会重用跳过的间隙值。并发插入时，新生成的IDENTITY值永远会大于之前所有已存在（包括已提交和未提交）的IDENTITY值，所以你完全可以通过保存已处理的最大IDENTITY值，用WHERE ID > @LastProcessedMaxID来安全获取所有新插入的事件，不会遗漏任何行。

1. IDENTITY列的生成逻辑保证单调性 #

SQL Server为IDENTITY列分配值的逻辑是：

• 每个插入操作（即使是并发的）会在执行阶段而非提交阶段获取下一个可用的IDENTITY值，这个值是全局单调递增的。
• 即使某个插入事务回滚，分配出去的IDENTITY值也不会被回收重用——不管是因为标识缓存（identity cache）导致的间隙，还是回滚、删除操作留下的间隙，这些值永远不会被填充。
• 并发会话之间不会争夺同一个IDENTITY值，SQL Server内部会维护一个全局的计数器，确保每个会话拿到的都是下一个未被使用的递增数值。

2. 需要警惕的边缘场景和破坏操作 #

虽然默认配置是安全的，但有两种操作会直接打破这个逻辑，必须严格避免：

（1）启用IDENTITY_INSERT手动插入ID #

当你执行SET IDENTITY_INSERT Events ON后，允许手动向IDENTITY列插入任意数值——包括比当前表中最大IDENTITY值更小的数。如果有人这样操作，就会出现新插入的行ID小于已存在行的情况，直接破坏你的事件追踪逻辑。

• 规避方式：除非有绝对必要的场景（比如数据迁移），否则永远不要启用IDENTITY_INSERT；如果必须使用，要确保手动插入的ID严格大于当前表的最大IDENTITY值，并且操作后立即关闭IDENTITY_INSERT。

（2）错误地重置IDENTITY种子（RESEED） #

执行DBCC CHECKIDENT ('Events', RESEED, N)会重置IDENTITY列的种子值。如果N小于当前表中已存在的最大IDENTITY值，后续插入生成的ID可能会：

• 生成小于现有最大ID的数值（比如现有最大ID是1000，RESEED到500，下一个生成的ID是501），如果这个ID不在间隙中，会触发主键冲突报错；如果在间隙中，就会出现新ID小于已存在大ID的情况。
• 规避方式：永远不要将IDENTITY种子重置为小于当前最大ID的数值；如果需要重置，必须先确认当前最大ID，确保新种子值大于该数值。

3. 额外配置说明 #

默认情况下，SQL Server的IDENTITY行为就完全符合你的需求，不需要额外配置。不过有一点需要注意：

• 标识缓存（identity cache）是默认启用的（SQL Server 2012及以后），它会导致服务器重启或故障时出现较大的间隙，但正如你所说，间隙本身不影响你的逻辑——只要这些间隙不会被填充，就不会破坏单调性，而SQL Server确实不会填充这些间隙。

事件存储场景的实践建议 #

用IDENTITY列作为事件存储的position是非常常见且成熟的做法，很多生产环境的事件溯源系统都在这么用。你可以放心地实现以下逻辑：

1. 每次处理事件后，保存当前处理过的最大IDENTITY值（比如存在本地配置或另一个状态表）。
2. 下次拉取新事件时，查询SELECT * FROM Events WHERE ID > @LastProcessedMaxID ORDER BY ID，这样就能获取所有未处理的事件，且不会遗漏任何并发插入的行。

内容的提问来源于stack exchange，提问作者Balinth