SQL DBCC CHECKIDENT 将标识设置为 0

DBCC CHECKIDENT Sets Identity to 0

提问by user47437

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.

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

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

采纳答案by kristof

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 truncateto clean the table instead of delete. But then you need to drop all the constraints and recreate them afterwards

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

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。第一个标识值将是表定义中指定的值，无论您是第一次插入数据还是第N次插入数据，它都是相同的

回答by Zyphrax

You are right in what you write in the edit of your question.

您在编辑问题时所写的内容是正确的。

After running DBCC CHECKIDENT('TableName', RESEED, 0):
- Newly created tables will start with identity 0
- Existing tables will continue with identity 1

运行后DBCC CHECKIDENT('TableName', RESEED, 0)：
- 新创建的表将以标识 0 开始
- 现有表将以标识 1 继续

The solution is in the script below, it's sort of a poor-mans-truncate :)

解决方案在下面的脚本中，它有点像穷人的截断:)

-- Remove all records from the Table
DELETE FROM TableName

-- Use sys.identity_columns to see if there was a last known identity value
-- for the Table. If there was one, the Table is not new and needs a reset
IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'TableName' AND last_value IS NOT NULL) 
    DBCC CHECKIDENT (TableName, RESEED, 0);

回答by dmajkic

Change statement to

将语句更改为

  DBCC CHECKIDENT('TableName', RESEED, 1)

This will start from 2 (or 1 when you recreate table), but it will never be 0.

这将从 2（或重新创建表时为 1）开始，但永远不会为 0。

回答by Ashish Kumar Jaryal

I did this as an experiment to reset the value to 0 as I want my first identity column to be 0 and it's working.

我这样做是为了将值重置为 0，因为我希望我的第一个标识列为 0 并且它正在工作。

dbcc CHECKIDENT(MOVIE,RESEED,0)
dbcc CHECKIDENT(MOVIE,RESEED,-1)
DBCC CHECKIDENT(MOVIE,NORESEED)

回答by Matthew Wise

It seems ridiculous that you can't set/reset an identity column with a single command to cover both cases of whether or not the table has had records inserted. I couldn't understand the behavior I was experiencing until I stumbled across this question on SO!

您无法使用单个命令设置/重置标识列来涵盖表是否已插入记录的两种情况，这似乎很荒谬。我无法理解我正在经历的行为，直到我在 SO 上偶然发现了这个问题！

My solution (ugly but works) is to explicitly check the sys.identity_columns.last_valuetable (which tells you whether or not the table has had records inserted) and call the appropriate DBCC CHECKIDENTcommand in each case. It is as follows:

我的解决方案（丑陋但有效）是显式检查sys.identity_columns.last_value表（它告诉您该表是否插入了记录）并DBCC CHECKIDENT在每种情况下调用适当的命令。如下：

DECLARE @last_value INT = CONVERT(INT, (SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'MyTable'));
IF @last_value IS NULL
    BEGIN
        -- Table newly created and no rows inserted yet; start the IDs off from 1
        DBCC CHECKIDENT ('MyTable', RESEED, 1);
    END
ELSE
    BEGIN
        -- Table has rows; ensure the IDs continue from the last ID used
        DECLARE @lastValUsed INT = (SELECT ISNULL(MAX(ID),0) FROM MyTable);
        DBCC CHECKIDENT ('MyTable', RESEED, @lastValUsed);
    END

回答by SQLMenace

See also here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/06/26/fun-with-dbcc-chekident.aspx

另见此处：http: //sqlblog.com/blogs/alexander_kuznetsov/archive/2008/06/26/fun-with-dbcc-chekident.aspx

This is documented behavior, why do you run CHECKIDENT if you recreate the table, in that case skip the step or use TRUNCATE (if you don't have FK relationships)

这是记录在案的行为，如果您重新创建表，为什么要运行 CHECKIDENT，在这种情况下跳过该步骤或使用 TRUNCATE（如果您没有 FK 关系）

回答by user2284063

I have used this in SQL to set IDENTITY to a particular value:-

我在 SQL 中使用它来将 IDENTITY 设置为特定值：-

DECLARE @ID int = 42;
DECLARE @TABLENAME  varchar(50) = 'tablename'

DECLARE @SQL nvarchar(1000) = 'IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = '''+@TABLENAME+''' AND last_value IS NOT NULL)
    BEGIN
        DBCC CHECKIDENT('+@TABLENAME+', RESEED,' + CONVERT(VARCHAR(10),@ID-1)+');
    END
    ELSE
    BEGIN
        DBCC CHECKIDENT('+@TABLENAME+', RESEED,' + CONVERT(VARCHAR(10),@ID)+');
    END';
EXEC (@SQL);

And this in C# to set a particular value:-

这在 C# 中设置特定值：-

SetIdentity(context, "tablename", 42);
.
.
private static void SetIdentity(DbContext context, string table,int id)
{
    string str = "IF EXISTS (SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = '" + table
        + "' AND last_value IS NOT NULL)\nBEGIN\n";
    str += "DBCC CHECKIDENT('" + table + "', RESEED," + (id - 1).ToString() + ");\n";
    str += "END\nELSE\nBEGIN\n";
    str += "DBCC CHECKIDENT('" + table + "', RESEED," + (id).ToString() + ");\n";
    str += "END\n";
    context.Database.ExecuteSqlCommand(str);
}

This builds on the above answers and always makes sure the next value is 42 (in this case).

这建立在上述答案的基础上，并始终确保下一个值是 42（在这种情况下）。

回答by vishalcode

Simply do this:

只需这样做：

IF EXISTS (SELECT * FROM tablename)
BEGIN
    DELETE from  tablename
    DBCC checkident ('tablename', reseed, 0)
END

回答by jjee

Borrowing from Zyphrax's answer ...

借用 Zyphrax 的回答...

USE DatabaseName

DECLARE @ReseedBit BIT = 
    COALESCE((SELECT SUM(CONVERT(BIGINT, ic.last_value))
                FROM sys.identity_columns ic
                INNER JOIN sys.tables t ON ic.object_id = t.object_id), 0)
DECLARE @Reseed INT = 
CASE 
    WHEN @ReseedBit = 0 THEN 1 
    WHEN @ReseedBit = 1 THEN 0 
END

DBCC CHECKIDENT ('dbo.table_name', RESEED, @Reseed);

Caveats: This is intended for use in reference data population situations where a DB is being initialized with enum type definition tables, where the ID values in those tables must always start at 1. The first time the DB is being created (e.g. during SSDT-DB publishing) @Reseed must be 0, but when resetting the data i.e. removing the data and re-inserting it, then @Reseed must be 1. So this code is intended for use in a stored procedure for resetting the DB data, which can be called manually but is also called from the post-deployment script in the SSDT-DB project. In that way the reference data inserts are only defined in one place but aren't restricted to be used only in post-deployment during publishing, they are also available for subsequent use (to support dev and automated test etc.) by calling the stored procedure to reset the DB back to a known good state.

警告：这旨在用于使用枚举类型定义表初始化 DB 的引用数据填充情况，其中这些表中的 ID 值必须始终从 1 开始。第一次创建 DB 时（例如，在 SSDT- DB 发布）@Reseed 必须为 0，但是在重置数据时，即移除数据并重新插入它时，那么@Reseed 必须为 1。所以此代码旨在用于重置 DB 数据的存储过程，它可以手动调用，但也从 SSDT-DB 项目中的部署后脚本调用。通过这种方式，参考数据插入仅在一个地方定义，但不限于仅在发布期间的后期部署中使用，它们也可用于后续使用（以支持开发和自动化测试等。

回答by user7208444

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 0);  
GO 



AdventureWorks2012=Your databasename
Person.AddressType=Your tablename