SQL身份(1,1)从0开始 [英] SQL identity (1,1) starting at 0

问题描述

我有一个设置了身份的SQL表:

I have a SQL table with an identity set:

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.

插入时:

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

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

这是一个问题，因为此插入上方的代码期望第一个ID为1-我不知道使用IDENTITY(1,1)怎么会以0结束.

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.

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

If (before executing the INSERT) I check the identity it returns null:

DBCC CHECKIDENT (MyTable, NORESEED)

检查身份信息:当前身份值'NULL'，当前列值'NULL'.

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

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

I know several ways to fix this; what I need to know how the table got into this state in the first place?

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

The only way I know that CHECKIDENT returns null is if the table's just been created, but then IDENTITY(1,1) is honoured and the INSERT causes SCOPE_IDENTITY() to be 1.

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

Alternatively I can get 0 as the next ID if I force -1 as the current seed (DBCC CHECKIDENT (MyTable, RESEED, -1) or with SET IDENTITY_INSERT MyTable ON) but then the check reports that current -1 seed (rather than null), so that can't be what's happened.

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

How did the database get into a state where the column has IDENTITY(1,1), DBCC CHECKIDENT (MyTable, NORESEED) returns null, but the next INSERT causes SCOPE_IDENTITY() to be 0?

推荐答案

我希望有人/某事已经运行:

I expect someone/something has run:

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:

检查身份信息:当前身份值'NULL'，当前列值'NULL'.

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

但是下一个标识值将为0.这是记录的行为，MSDN指出:

However the next identity value will be 0. This is documented behaviour, MSDN states:

当前标识值设置为new_reseed_value. 如果自创建表以来未将任何行插入到表中，则在执行DBCC CHECKIDENT之后插入的第一行将使用new_reseed_value作为标识.否则，插入的下一行将使用new_reseed_value +1.如果new_reseed_value的值小于标识列中的最大值，则在对该表的后续引用上将生成错误消息2627.

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.

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

This only works on newly created/truncated tables where the last_value column in sys.identity_columns is 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.

完整测试脚本

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

这篇关于SQL身份(1,1)从0开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！