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
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)
    if (@pk_id is null)
        set nocount on;

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

        select @pk_id

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.


3 Answers 3


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
            , SomeText nvarchar(255)

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

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

enter image description here

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;

enter image description here

  • 4
    Only if the table is newly created or has been truncated. If you insert a row and delete it you get a different result CREATE TABLE dbo.[table] (id int identity(1,1));INSERT INTO dbo.[table] DEFAULT VALUES;DELETE FROM dbo.[table];dbcc checkident('dbo.table',reseed,0);INSERT INTO dbo.[table] OUTPUT inserted.* DEFAULT VALUES;DROP TABLE dbo.[table] Apr 12, 2013 at 13:48
  • I concur, Martin. I believe the only way to get a 0 in an identity field would be to start with an empty/truncated table.
    – Hannah Vernon
    Apr 12, 2013 at 13:53
  • @MartinSmith deleting the record and reseeding is the pattern that i've been following. Truncate doesn't work on most of our tables since they have FK relationships.
    – DForck42
    Apr 12, 2013 at 13:54

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

DELETE FROM dbo.table
    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".

(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,
רועי גביש

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)
  • yes, the first one sets the id to 0, so when the next record is inserted sql server takes the current id, adds 1, and gets 1. the next one the first id i would get is 2.
    – DForck42
    Apr 26, 2013 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.