SQL 不会在 INSERT 错误时增加主键
今天有点惊讶地发现，当插入语句错误时，我的自动递增主键仍然会递增，结果是我们的表中“缺少”数字。
SQL 如此设计有什么原因吗？有什么方法可以覆盖这种行为吗？
我通过 Linq-to-SQL 进行访问。
Was a bit suprised today to discover that my auto-increment primary key still gets incremented when an insert statement errors, with the result that we have "missing" numbers in our table.
Is there any reason why SQL was designed this way? And is there any way to override this behaviour?
I am accessing through Linq-to-SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是设计用于处理并发性并使其尽可能快（如果同时发生 3 个插入并且回滚 1 个会发生什么..这会搞乱一切）..当插入开始时 sql server 确定身份的值..如果发生回滚，这些值不会被重用..除非您想在每次回滚后运行 DBCC CHECKIDENT，否则您实际上无能为力。
如果您需要一个能够识别间隙的查询，请查看此< a href="http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/how-to-return-all-the-skipped-identity-v" rel="noreferrer">如何返回所有跳过的SQL Server 中表中的标识值
SQL Server 的下一个版本将具有
序列，这可能会帮助您处理其中一些内容，请查看此处：初步了解 SQL Server Denali 中的序列this is by design to deal with concurrency and make it as fast as possible (what would happen if 3 inserts happens at the same time and 1 is rolled back..that would mess up everything)..when an inserts starts sql server determines the value of the identity..if a rollback happens those values are not reused..there is nothing you can really do unless you want to run DBCC CHECKIDENT after every rollback
If you need a query that will identify gaps, take a look at this How to return all the skipped identity values from a table in SQL Server
The next version of SQL Server will have
sequenceswhich might help you with some of this stuff, take a look here: A first look at sequences in SQL Server Denali