SQL身份(1,1)从0开始

Question

我有一个带有标识集的SQL表:

CREATE TABLE MyTable(
    MyTableID int IDENTITY(1,1) NOT NULL,
    RecordName nvarchar(100) NULL)

这张 table 发生了什么事，导致行为异常。我需要找出什么。

插入时:

INSERT MyTable(RecordName) 
VALUES('Test Bug')

SELECT SCOPE_IDENTITY() -- returns 0
SELECT * FROM MyTable   -- displays: 0, 'Test Bug'

这是一个问题，因为此插入上方的代码期望第一个ID为 1-我不知道如何使用 IDENTITY(1,1)将其最终表示为 0。

如果(在执行 INSERT之前)我检查了身份，则返回null:

DBCC CHECKIDENT (MyTable, NORESEED)

Checking identity information: current identity value 'NULL', current column value 'NULL'.

我知道几种解决此问题的方法；我首先需要知道表格如何进入此状态？

我知道 CHECKIDENT返回null的唯一方法是是否刚刚创建了表，但是兑现了 IDENTITY(1,1)，并且 INSERT导致 SCOPE_IDENTITY()为 1。

或者，如果我将 0强制为当前种子( -1或带有 DBCC CHECKIDENT (MyTable, RESEED, -1))，则可以将 SET IDENTITY_INSERT MyTable ON作为下一个ID，但随后检查报告该当前 -1种子(而不是null)，所以这不可能发生。

数据库如何进入列包含 IDENTITY(1,1)的状态， DBCC CHECKIDENT (MyTable, NORESEED)返回null，但是下一个 INSERT导致 SCOPE_IDENTITY()为 0？

Answer 1

我希望有人/某事已经发生:

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0);

如果运行以下命令:

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);

第二个 CHECKIDENT仍返回 NULL:

Checking identity information: current identity value 'NULL', current column value 'NULL'.

但是，下一个标识值将为0。这是 documented behaviour，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.

这仅适用于 last_value中 sys.identity_columns列仍为NULL的新创建/截断的表。如上所述，如果要插入一行，将其删除，然后重新设置为0，则新标识仍为1。

完整测试脚本

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