What could cause the wrong ID to be inserted?

Question

I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it:

delete from dbo.table
go    
dbcc checkident('dbo.table',reseed,0)

When the user went to create a new record later on, somehow an ID of 0 was inserted into the ID column, instead of the 1 SQL Server usually puts in if identity(1,1) is configured for the ID.

This caused some weird issues, but clearing the data and running the reseed resulted in a 1 being inserted, as expected. I can't duplicate the issue.

For reference, here's the general format for our save sp's:

alter procedure dbo._TableSave
    @pk_id int,
    @field varchar(50)
as
    if (@pk_id is null)
    begin
        set nocount on;

        insert into dbo.Table
        (
            Field
        )
        values
        (
            @field
        );
        select scope_identity();
    end
    else
    begin
        update dbo.Table
        set Field=@field
        where PK_ID=@pk_id

        select @pk_id
    end

Does anyone know what could cause SQL Server to insert a 0 in the ID when it should have been a 1?

We are not inserting data into identity columns (with identity_insert on) anywhere in the application.

Answer 1

Doing dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity.

        CREATE TABLE TestIdent
        (
            ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
            , SomeText nvarchar(255)
        );

        dbcc checkident('dbo.TestIdent',reseed,0)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

This causes the next identity entered to be 10:

        TRUNCATE TABLE dbo.TestIdent;

        DBCC CHECKIDENT('dbo.TestIdent',reseed,10)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

Answer 2

I suggest the following code to prevent it from happening:
(Instead of the code you posted in your question)

DELETE FROM dbo.table
GO
IF EXISTS 
(
    SELECT * 
    FROM sys.identity_columns 
    WHERE object_id = OBJECT_ID('dbo.table') 
        AND last_value IS NOT NULL
)
DBCC CHECKIDENT ('dbo.table', RESEED, 0);

It should prevent the "reseeding-to-0".

Explanation:
(I have found no official documentation to support it...)
The problem is with the table sys.syscolpars in a column named idtval
that is the column which holds ALL the identity column information.
since that table has a row for each column in the database (not only identity columns),
a NULL value in that field will represent a non-identity column
(unlike sys.identity_columns which uses NULL for a new\truncated table).

sys.syscolpars uses a different method for holding the information:
it uses the 0x01000000010000000100000001 format (example of identity(1,1) newly created),
with the left 8 digits(position 1-8 after the 0x) for Last_Value,
the next 8 digits (position 9-16 after the 0x)for increment_value,
the next 8 digits (position 17-24 after the 0x)for seed_value,
the 2nd digit from the right is a mystery to me,
and the 1st digit from the right - tells us if it is a new table(=1) or not!

back to our issue -
when DBCC CHECKIDENT is run, it checks sys.identity_columns.last_value
if it is NULL then it updates the new value of sys.syscolpars.idtval with 1 in the right position.
sys.identity_columns.last_value remains NULL.
that causes the next insert identity value to be treated as in a new table.
(I assume sys.identity_columns is independent of sys.syscolpars because the first is updated immediatly, and the latter is only updated after a checkpoint is performed).

Since there is no way of querying sys.syscolpars in real time (it requires DAC),
my suggestion is the condition above, maybe with the addition of ELSE clause.

again, i cannot guarantee that any of this explanation is true, but it does work :)

If anyone has a more accurate information about the DBCC CHECKIDENT background operation,
i would love to learn, please share!

Good luck,
רועי גביש

Answer 3

dbcc checkident('dbo.table',reseed,0)

this command cause the new identity value to set to 0. I have faced the same problem.

then i apply reseed identity to 1 like this

  dbcc checkident('dbo.table',reseed,1)