HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

What could cause the wrong ID to be inserted?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thewhatcouldinsertedwrongcause

Problem

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.

Solution

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;

Code Snippets

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;
TRUNCATE TABLE dbo.TestIdent;

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

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

Context

StackExchange Database Administrators Q#39824, answer score: 10

Revisions (0)

No revisions yet.