SQL identity (1,1) starting at 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
Keith
Keith Henry Chief Software Architect, building offline-first and responsive applications in the recruitment industry. I'm also on Linked In. Email me on Google's email, my address is ForenameSurname.
Updated on July 09, 2022Comments
-
Keith almost 2 years
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 withIDENTITY(1,1)
this ends up as0
.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 thenIDENTITY(1,1)
is honoured and theINSERT
causesSCOPE_IDENTITY()
to be1
.Alternatively I can get
0
as the next ID if I force-1
as the current seed (DBCC CHECKIDENT (MyTable, RESEED, -1)
or withSET 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 nextINSERT
causesSCOPE_IDENTITY()
to be0
?