SQL Server 标识列值从 0 而不是 1 开始
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/724668/
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
SQL server identity column values start at 0 instead of 1
提问by Muxa
I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today.
即使 TABLE CREATE 有 IDENTITY(1,1)，我的数据库中有一些表的 ID 从 0 开始，我遇到了一个奇怪的情况。对于某些表是这样，但对于其他表则不然。它一直工作到今天。
I've tried resetting identity column:
我试过重置身份列：
DBCC CHECKIDENT (SyncSession, reseed, 0);
But new records start with 0. I have tried doing this for all tables, but some still start from 0 and some from 1.
但是新记录从 0 开始。我尝试对所有表都这样做，但有些仍然从 0 开始，有些从 1 开始。
Any pointers?
任何指针？
(i'm using SQL Server Express 2005 with Advanced Services)
（我使用的是带有高级服务的 SQL Server Express 2005）
回答by gbn
From DBCC CHECKIDENT
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )
If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
如果表创建后没有插入任何行，或者使用 TRUNCATE TABLE 语句删除了所有行，则在运行 DBCC CHECKIDENT 后插入的第一行使用 new_reseed_value 作为标识。否则，插入的下一行使用 new_reseed_value + 当前增量值。
So, this is expected for an empty or truncated table.
因此，这是空表或截断表的预期结果。
回答by Keith
If you pass a reseed value the DB will start the identity from that new value:
如果您传递重新种子值，数据库将从该新值开始标识：
DBCC CHECKIDENT (SyncSession, RESEED, 0); --next record should be 0 + increment
You don't have to pass the a value though, if you don't IDENTITY(a,b)will be used instead:
不过，您不必传递 a 值，如果不传递，IDENTITY(a,b)将改为使用：
DBCC CHECKIDENT (SyncSession, RESEED); --next record should be the seed value 'a'
This is usually better practice, as it leaves the table closer to its initial created state.
这通常是更好的做法，因为它使表更接近其初始创建状态。
回答by Frederik Gheysels
This is logical, since you've changed (reseeded) the identity value to zero ?
这是合乎逻辑的，因为您已将标识值更改（重新播种）为零？
DBCC CHECKIDENT (SyncSession, reseed, 1)
will reseed your identity column, and make sure that the first new record will start with 1.
将重新设定您的身份列，并确保第一条新记录以 1 开头。
回答by Larry
I have the same problem, restoring from a backup after modifying the DB. I just add a dummy record and then delete it... then set RESEED to 0. Seems to work.
我有同样的问题，修改数据库后从备份中恢复。我只是添加一个虚拟记录，然后将其删除...然后将 RESEED 设置为 0。似乎有效。
回答by Tanveer
Try this
尝试这个
DECLARE @c TABLE (TanvtechId varchar(10),NewTanvtechId Varchar(10))
INSERT INTO @c
SELECT TanvtechId , Row_Number() OVER (ORDER BY TanvtechId ) from Tanvtech
UPDATE G
SET G.TanvtechId =a.NewTanvtechId
FROM Tanvtech as G INNER JOIN @c as a ON a.TanvtechId =G.TanvtechId
回答by Azam
DBCC CHECKIDENT ( Table_Name, RESEED, 0 )
This is a way to start an idwith Zero(0), then delete all the rows from table and again put the data back into the table.
这是一种以idwith开头的方法Zero(0)，然后从表中删除所有行，然后再次将数据放回表中。
