DBCC CHECKIDENT将标识设置为0(DBCC CHECKIDENT Sets Identity to 0)

DBCC CHECKIDENT将标识设置为0(DBCC CHECKIDENT Sets Identity to 0)

我正在使用此代码重置表上的身份：

DBCC CHECKIDENT('TableName', RESEED, 0)

这样大部分时间都可以工作，第一个插入我在Id列中插入1。 但是，如果我删除db并重新创建它（使用我写的脚本），然后调用DBCC CHECKIDENT，插入的第一个项目的ID为0。

有任何想法吗？

编辑：经过研究发现我没有正确阅读文档： http : //msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx - “当前身份值设置为new_reseed_value。如果没有任何行被创建后插入到表中，执行DBCC CHECKIDENT后插入的第一行将使用new_reseed_value作为标识，否则插入的下一行将使用new_reseed_value + 1。

I'm using this code to reset the identity on a table:

DBCC CHECKIDENT('TableName', RESEED, 0)

This works fine most of the time, with the first insert I do inserting 1 into the Id column. However if I drop the db and recreate it (using scripts I've written) and then call DBCC CHECKIDENT, the first item inserted will have an ID of 0.

Any ideas?

EDIT: After researching I found out I didn't read the documentation properly: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx - "The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. "

最满意答案

正如您在问题中指出的那样，它是一种记录的行为 。 我仍然觉得很奇怪。 我用来重新填充测试数据库，尽管我不依赖于标识字段的值，但是从头开始首次填充数据库以及在删除所有数据并重新填充数据库后，具有不同的值是有点烦人的。

一个可能的解决方案是使用truncate来清理表而不是删除。 但是，然后你需要删除所有的约束并重新创建它们

以这种方式，它始终表现为新创建的表，并且不需要调用DBCC CHECKIDENT。 第一个标识值将是表定义中指定的值，它将是相同的，无论您是第一次插入数据还是第N个

As you pointed out in your question it is a documented behavior. I still find it strange though. I use to repopulate the test database and even though I do not rely on the values of identity fields it was a bit of annoying to have different values when populating the database for the first time from scratch and after removing all data and populating again.

A possible solution is to use truncate to clean the table instead of delete. But then you need to drop all the constraints and recreate them afterwards

In that way it always behaves as a newly created table and there is no need to call DBCC CHECKIDENT. The first identity value will be the one specified in the table definition and it will be the same no matter if you insert the data for the first time or for the N-th