Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you completely ruled out the possibility of an IDENTITY_INSERT somewhere? –  JNK Apr 24 '13 at 15:36
    
@jnk we are not inserting data into identity columns anywhere in the application. –  DForck42 Apr 24 '13 at 16:36
    
@DForck42 please read my answer below, i edited it and added some relevant info. hope that helps. –  Justicator Apr 29 '13 at 18:32
add comment

3 Answers

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

share|improve this answer
    
I'm curious about why this works. Can you add some detail explaining how this works, and why it would prevent a 0 being generated instead of the expected 1? –  Max Vernon Apr 24 '13 at 19:14
1  
@MaxVernon I added my explanation, please feel free to review and proof-read my addition :) –  Justicator Apr 29 '13 at 17:27
    
Wow, that's some in-depth information. Thanks for adding that! –  Max Vernon Apr 29 '13 at 18:03
    
Interesting. I am going to look into this. –  DForck42 Apr 29 '13 at 20:58
add comment
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)
share|improve this answer
    
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 '13 at 16:35
add comment

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;

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

share|improve this answer
3  
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] –  Martin Smith Apr 12 '13 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. –  Max Vernon Apr 12 '13 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 '13 at 13:54
    
I edited my question to include that my table already had data in it when i deleted the data. –  DForck42 Apr 12 '13 at 13:55
3  
@DForck42 how often are you doing this and why? –  Aaron Bertrand Apr 12 '13 at 13:59
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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