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
?
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