SQL Server - 即使在回滚的情况下如何确保标识字段正确增加
在 SQL Server 中，如果涉及插入新行的事务被回滚，则标识字段中的数字将被跳过。
例如，如果 Foos 表中的最高 ID 是 99，那么我们尝试插入新的 Foo 记录但回滚，则 ID 100 会“用完”，并且下一个 Foo 行将编号为 101。
有什么方法可以更改此行为，以便保证标识字段是连续的？
In SQL Server, if a transaction involving the inserting of a new row gets rolled back, a number is skipped in the identity field.
For example, if the highest ID in the Foos table is 99, then we try to insert a new Foo record but roll back, then ID 100 gets 'used up' and the next Foo row will be numbered 101.
Is there any way this behaviour can be changed so that identity fields are guaranteed to be sequential?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你所追求的永远不会与身份列一起使用。
它们被设计为“放弃”并忘记，这样就不会导致等待或死锁等。该属性允许将 IDENTITY 列用作高度事务性系统中的序列，而不会出现延迟或瓶颈。
确保没有间隙意味着无法实现每秒 100 次插入的系统，因为将有一个很长的队列来确定第一个插入是否要回滚。
出于同样的原因，您通常不希望出现这种行为，也不希望在大容量表中出现这样的数字序列。但是，对于非常罕见的单进程表（例如每月单个进程的发票编号），可以将事务围绕
MAX(number)+1或类似查询进行处理，例如What you are after will never work with identity columns.
They are designed to "give out" and forget, by-design so that they don't cause waits or deadlocks etc. The property allows IDENTITY columns to be used as a sequence within a highly transactional system with no delay or bottlenecks.
To make sure that there are no gaps means that there is NO WAY to implement a 100-insert per second system because there would be a very long queue to figure out if the 1st insert was going to be rolled back.
For the same reason, you normally do not want this behaviour, nor such a number sequence for a high volume table. However, for very infrequent, single-process tables (such as invoice number by a single process monthly), it is acceptable to put a transaction around a
MAX(number)+1or similar query, e.g.即使事务回滚，SQL 标识（自动编号）也会增加
SQL Identity (autonumber) is Incremented Even with a Transaction Rollback