DBCC CHECKIDENT 将标识设置为 0 [英] DBCC CHECKIDENT Sets Identity to 0

问题描述

我正在使用此代码重置表上的标识:

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

DBCC CHECKIDENT('TableName', RESEED, 0)

这在大多数情况下都可以正常工作，第一次插入时我将 1 插入到 Id 列中.但是，如果我删除 DB 并重新创建它(使用我编写的脚本)，然后调用 DBCC CHECKIDENT，则插入的第一个项目的 ID 将为 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.

有什么想法吗?

研究后我发现我没有阅读 文档 正确 - 当前标识值设置为 new_reseed_value.如果表创建后没有插入任何行，则执行 DBCC CHECKIDENT 后插入的第一行将使用 new_reseed_value 作为标识.否则，插入的下一行将使用 new_reseed_value + 1. "

After researching I found out I didn't read the documentation properly - "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. "

推荐答案

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

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.

一种可能的解决方案是使用 truncate 来清理表而不是删除.但是随后您需要删除所有约束并在之后重新创建它们

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

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

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

这篇关于DBCC CHECKIDENT 将标识设置为 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！