SQL 删除行后重用标识值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4288089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reuse identity value after deleting rows
提问by Jeremy
Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I will have these 5 Ids: 1, 2, 3, 4, 5. If I were to delete these rows, and then add five more, the new rows would have Ids: 6, 7, 8, 9, 10. Is it possible to let it start over at 1 again?
在 SQL Server 2008 Express 中删除行后是否可以重用标识字段值？这是一个例子。假设我有一个带有 Id 字段作为主键（身份）的表。如果我添加五行，我将有这 5 个 Id：1, 2, 3, 4, 5。如果我要删除这些行，然后再添加五行，新行的 Ids 为：6, 7, 8， 9, 10. 是否可以让它从 1 重新开始？
Do I have to delete data from another table in order to accomplish this? Thanks for your help.
我是否必须从另一个表中删除数据才能完成此操作？谢谢你的帮助。
回答by OMG Ponies
You can use the following to set the IDENTITY value:
您可以使用以下方法设置 IDENTITY 值：
DBCC CHECKIDENT (orders, RESEED, 999)
That means you'll have to run the statement based on every DELETE. That should start to highlight why this is a bad idea...
这意味着您必须根据每个 DELETE 运行语句。这应该开始强调为什么这是一个坏主意......
The database doesn't care about sequential values - that's for presentation only.
数据库不关心顺序值 - 这仅用于演示。
回答by gbn
If you want to reset the identity after deleting allrows then do one of these
如果您想在删除所有行后重置身份，请执行以下操作之一
--instead of delete, resets identity value
TRUNCATE TABLE orders
--or if TRUNCATE fails because of FKs, use this after DELETE
DBCC CHECKIDENT (orders, RESEED, 1)
Otherwise, the internal value should not matter whether gaps or not.
否则，内部值应该无关紧要是否有差距。
回答by Donnie
identityfields do not reuse old values by default. You can reseed them with dbcc checkident, but this isn't suggested as you will get key violations if you reseed below a value that still exists in the table. In general, you shouldn't care what the PK values are. The fact that they're not contiguous doesn't hurt anything.
identity默认情况下，字段不重用旧值。您可以使用 为它们重新设定种子dbcc checkident，但不建议这样做，因为如果您在表中仍然存在的值以下重新设定种子，则会导致密钥违规。通常，您不应该关心 PK 值是什么。它们不连续的事实不会伤害任何东西。
