SQL 标识 (1,1) 从 0 开始
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22199917/
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 identity (1,1) starting at 0
提问by Keith
I have a SQL table with an identity set:
我有一个带有标识集的 SQL 表：
CREATE TABLE MyTable(
MyTableID int IDENTITY(1,1) NOT NULL,
RecordName nvarchar(100) NULL)
Something has happened to this table, resulting in odd behaviour. I need to find out what.
这张桌子发生了一些事情，导致了奇怪的行为。我需要找出什么。
When an insert occurs:
当插入发生时：
INSERT MyTable(RecordName)
VALUES('Test Bug')
SELECT SCOPE_IDENTITY() -- returns 0
SELECT * FROM MyTable -- displays: 0, 'Test Bug'
This is a problem because code above this insert expects the first ID to be 1- I can't figure out how with IDENTITY(1,1)this ends up as 0.
这是一个问题，因为此插入上方的代码期望第一个 ID 为1- 我无法弄清楚它如何IDENTITY(1,1)最终为0.
If (before executing the INSERT) I check the identity it returns null:
如果（在执行之前INSERT）我检查身份，它返回空：
DBCC CHECKIDENT (MyTable, NORESEED)
Checking identity information: current identity value 'NULL', current column value 'NULL'.
检查标识信息：当前标识值“NULL”，当前列值“NULL”。
I know several ways to fix this; what I need to know how the table got into this state in the first place?
我知道有几种方法可以解决这个问题；我首先需要知道桌子是如何进入这种状态的？
The only way I know that CHECKIDENTreturns null is if the table's just been created, but then IDENTITY(1,1)is honoured and the INSERTcauses SCOPE_IDENTITY()to be 1.
我知道CHECKIDENT返回 null的唯一方法是，如果表刚刚创建，但是IDENTITY(1,1)很荣幸并且INSERT原因SCOPE_IDENTITY()是1.
Alternatively I can get 0as the next ID if I force -1as the current seed (DBCC CHECKIDENT (MyTable, RESEED, -1)or with SET IDENTITY_INSERT MyTable ON) but then the check reports that current -1seed (rather than null), so that can't be what's happened.
或者，0如果我强制-1作为当前种子（DBCC CHECKIDENT (MyTable, RESEED, -1)或 with SET IDENTITY_INSERT MyTable ON），我可以获得下一个 ID ，但是检查报告当前-1种子（而不是空），所以这不可能是发生的事情。
How did the database get into a state where the column has IDENTITY(1,1), DBCC CHECKIDENT (MyTable, NORESEED)returns null, but the next INSERTcauses SCOPE_IDENTITY()to be 0?
数据库是如何进入列有IDENTITY(1,1),DBCC CHECKIDENT (MyTable, NORESEED)返回空，但下一个INSERT原因SCOPE_IDENTITY()是 的状态0？
回答by GarethD
I expect someone/something has run:
我希望有人/某事已经运行：
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0);
If you run the following:
如果您运行以下命令：
CREATE TABLE dbo.MyTable(
MyTableID int IDENTITY(1,1) NOT NULL,
RecordName nvarchar(100) NULL
);
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0);
DBCC CHECKIDENT ('dbo.MyTable', NORESEED);
The second CHECKIDENTstill returns NULL:
第二个CHECKIDENT仍然返回NULL：
Checking identity information: current identity value 'NULL', current column value 'NULL'.
检查标识信息：当前标识值“NULL”，当前列值“NULL”。
However the next identity value will be 0. This is documented behaviour, MSDN states:
然而，下一个标识值为 0。这是记录在案的行为，MSDN 声明：
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. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.
当前标识值设置为 new_reseed_value。如果表创建后没有插入任何行，则在执行 DBCC CHECKIDENT 后插入的第一行将使用 new_reseed_value 作为标识。否则，插入的下一行将使用new_reseed_value + 1。如果new_reseed_value 的值小于标识列中的最大值，则在后续引用该表时将生成错误消息2627。
This only works on newly created/truncated tables where the last_valuecolumn in sys.identity_columnsis still NULL. As described above if you were to insert a row, delete it, then reseed to 0, the new identity would still be 1.
这仅适用于新创建/截断的表，其中last_value列sys.identity_columns仍为 NULL。如上所述，如果您要插入一行，将其删除，然后重新设定为 0，则新标识仍为 1。
Full Test Script
完整的测试脚本
IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
DROP TABLE dbo.T;
CREATE TABLE dbo.T(ID INT IDENTITY(1,1) NOT NULL);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 1
DELETE dbo.T;
DBCC CHECKIDENT ('dbo.T', RESEED, 0);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 1
TRUNCATE TABLE dbo.T;
DBCC CHECKIDENT ('dbo.T', RESEED, 0);
INSERT dbo.T OUTPUT inserted.* DEFAULT VALUES;
-- OUTPUTS 0
