Sql-server – What could cause the wrong ID to be inserted

identitysql serversql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – Table with identity column in merge replication

When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur.

With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology.

With manual, you will need to manually assign "islands" of ranges for nodes using DBCC CHECKIDENT.

Have a look at Replicate Identity Columns.

Sql-server – Reseed a newly created table

This behaviour is documented, but counter-intuitive:

Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

It was acknowledged as a bug (on the now-defunct Microsoft Connect site), but not one that will be fixed. Workarounds include avoiding using the RESEED option on newly-created tables, or to use a higher value that will work in all cases but may result in gaps. Gaps are all but inevitable with IDENTITY anyway. If you must do the reseed, wrap it in a check like:

IF EXISTS 
(
    SELECT 1 
    FROM sys.identity_columns 
    WHERE 
        [object_id] = OBJECT_ID(N'dbo.T1', N'U')
        AND last_value IS NOT NULL
)
BEGIN
    DBCC CHECKIDENT('dbo.T1', 'RESEED', 0)
END;

To be clear, creating a table and adding a row will result in the first identity value being used:

-- Tested on SQL Server 2008 SP3 CU8
-- build 10.0.5828
CREATE TABLE dbo.T1 (id int IDENTITY(1, 1));
INSERT dbo.T1 DEFAULT VALUES;
SELECT id FROM dbo.T1 AS t;

Id 1

Related Question