SQL identity (1,1) starting at 0

Question

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.

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.

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

DBCC CHECKIDENT (MyTable, NORESEED)

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?

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.

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.

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?

Answer 1

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 CHECKIDENT still returns NULL:

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

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

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.

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.

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