为什么我的自动递增 ID 会跳过 SQL Server 中的数字? [英] Why does my auto-incremented Id skip numbers in SQL Server?

问题描述

而且它们似乎是相当大的跳跃，甚至有数千个.举个例子:

And they seem to be fairly large skips, in the thousands even. Here's an example:

该表的最后一个 ID 超过 30.000 标记，即使表中的行数少于一千.

The last Ids of this table go above the 30.000 mark even though there are less than a thousand rows in the table.

• 这是什么原因造成的?
• 我将来如何防止这种情况发生?
• 如何阻止我当前的桌子继续在这条黑暗的道路上前进?

这发生在我的一个旧的小型桌面应用程序中，我从未从该表中删除行，也没有使用任何事务和回滚.

This is happening in an old, small desktop application of mine and I have not ever deleted rows from this table nor have I used any transactions and rollbacks.

推荐答案

这是什么原因造成的?

What is causing this?

想到几个潜在的原因:

• 行被删除了吗?
• 您查看的结果不是按 id 排序的?
• 在未提交的事务中分配了标识符?
• 数据库引擎分配了潜在的标识符作为内部性能调整，但分配丢失了(例如，服务器意外重启)?

可能还有更多我没有想到的潜在原因.

There could be more potential causes I'm not thinking of.

我将来如何防止这种情况发生?

How can I prevent this in the future?

取决于原因.但这确实是一个有争议的问题.为什么需要防止这种情况发生"?这种情况究竟有什么问题?标识符不需要是连续的，它们只需要是唯一的.(最好是顺序的索引，否则数据库将不得不重新建立索引.)

Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)

我怎样才能阻止我当前的桌子继续走这条黑暗的道路?

How can I stop my current table from continuing on this dark road?

……生成唯一标识符的黑暗之路?好吧，我想您可以手动提供唯一标识符.GUID 非常适合这类事情.不过，有利也有弊.(我不知道最近的实现是否改善了这一点，但从历史上看，GUID 并不能成为一个好的聚集索引.)

The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)

这篇关于为什么我的自动递增 ID 会跳过 SQL Server 中的数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！