SQL identifier (1,1) starting with 0

Question

I have an SQL table with a set of identifiers:

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

Something happened to this table, which led to an odd behavior. I need to find out that.

When the 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 the code above this insertion expects the first identifier to be 1 - I cannot figure out how with IDENTITY(1,1) it ends up as 0 .

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

 DBCC CHECKIDENT (MyTable, NORESEED) 

Verification of identification information: the current value of the identifier is "NULL", the current value of the column is "NULL".

I know several ways to fix this; What do 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 has just been created, but then IDENTITY(1,1) , and INSERT calls SCOPE_IDENTITY() 1 .

Alternatively, I can get 0 as the next identifier, if as a current seed ( DBCC CHECKIDENT (MyTable, RESEED, -1) or with SET IDENTITY_INSERT MyTable ON ) I force -1 , but then checks that the current -1 seed ( rather than zero), so this cannot be.

How did the database get into a state where the column has IDENTITY(1,1) , DBCC CHECKIDENT (MyTable, NORESEED) returns null, and the next INSERT calls SCOPE_IDENTITY() as 0 ?

Answer 1

I expect someone / something to 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 :

Verification of identification information: the current value of the identifier is "NULL", the current value of the column is "NULL".

However, the following identity value will be 0. This is a documented behavior , MSDN states:

The current value of the identifier is set to new_reseed_value. If no row has been inserted into the table since it was created, the first row inserted after DBCC CHECKIDENT is executed will use new_reseed_value as the identifier . Otherwise, the next inserted row will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identification column, error message 2627 will be generated on subsequent links to the table.

This only works for 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, and then move it to 0, then the new identifier will be 1.

Full Script Test

 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