CODEWITHSUNDEEP
sqlsql-serversql-server-2005identityauto-increment
Muxa
Muxa

Reputation: 5643

SQL server identity column values start at 0 instead of 1

I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today.

I've tried resetting identity column:

DBCC CHECKIDENT (SyncSession, reseed, 0);

But new records start with 0. I have tried doing this for all tables, but some still start from 0 and some from 1.

Any pointers?

(i'm using SQL Server Express 2005 with Advanced Services)

Upvotes: 52

Views: 90837

Answers (8)

Leon Bouquiet
Leon Bouquiet

Reputation: 4372

As stated in other answers here, for a table that has just been created and has never had a row in it, performing DBCC CHECKIDENT seems to have strange effects. Furthermore, it shouldn't really be necessary, because the first row that is inserted will nicely adhere to its identity(a, b) specification.

Only when it has had rows in it (that have been deleted) you should reseed it, with the seed value being one less than the identity value you want to insert next. That is, to have the first inserted row get an identity value of 1, use:

DBCC CHECKIDENT ('MyTable', RESEED, 0);

Now, to tell these 2 situations apart, the following query will return null if MyTable never has any rows in it (and DBCC CHECKIDENT should not be used), or will return the last issued identity value otherwise (which should then be reset with DBCC CHECKIDENT):

select idc.last_value
from [sys].[objects] as obj
inner join [sys].[identity_columns] as idc on (obj.object_id = idc.object_id)
where obj.type = 'U' and obj.name = 'MyTable'

Combining these 2:

if (select idc.last_value
    from [sys].[objects] as obj
    inner join [sys].[identity_columns] as idc on (obj.object_id = idc.object_id)
    where obj.type = 'U' and obj.name = 'MyTable') is not null
begin
    -- Table has had at least one identity value generated, table needs to be reseeded.
    dbcc checkident ('MyTable', reseed, 0);
end

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109119

The currently accepted answer only explains this annoying phenomenon. Only one answer offers some sort of a solution, but not really practical because it requires a dummy insertion, which makes it hard to generalize.

The only generic solution is to reseed the identity value, then check the current identity value and reseed it again when it's 0. This can be done by a stored procedure:

CREATE OR ALTER PROCEDURE ReseedIdentity
    @tableName SYSNAME
AS
BEGIN
    DBCC CHECKIDENT(@tableName, RESEED, 0)
    IF IDENT_CURRENT(@tableName) = 0
    BEGIN
        DBCC CHECKIDENT(@tableName, RESEED, 1)
    END
END

This will always start new records at identity value 1, whether it's a new table, after truncating or after deleting all records.

If there are identity specifications starting at higher seed values a somewhat more advanced version can be used, which is a generalization of the former:

CREATE OR ALTER PROCEDURE ReseedIdentity
    @tableName SYSNAME
AS
BEGIN
    DECLARE @seed NUMERIC(18,0) = IDENT_SEED(@tableName) - 1;
    DBCC CHECKIDENT(@tableName, RESEED, @seed)
    IF IDENT_CURRENT(@tableName) = @seed
    BEGIN
        SET @seed = @seed + 1
        DBCC CHECKIDENT(@tableName, RESEED, @seed)
    END
END

Upvotes: 2

Azam
Azam

Reputation: 1

DBCC CHECKIDENT ( Table_Name, RESEED, 0 )

This is a way to start an id with Zero(0), then delete all the rows from table and again put the data back into the table.

Upvotes: -1

Keith
Keith

Reputation: 155692

If you pass a reseed value the DB will start the identity from that new value:

DBCC CHECKIDENT (SyncSession, RESEED, 0); --next record should be 0 + increment

You don't have to pass the a value though, if you don't IDENTITY(a,b) will be used instead:

DBCC CHECKIDENT (SyncSession, RESEED); --next record should be the seed value 'a'

This is usually better practice, as it leaves the table closer to its initial created state.

Upvotes: 8

Tanveer
Tanveer

Reputation: 11

Try this

DECLARE @c TABLE (TanvtechId varchar(10),NewTanvtechId Varchar(10))
INSERT INTO @c
SELECT TanvtechId , Row_Number() OVER (ORDER BY TanvtechId ) from Tanvtech 

UPDATE G
SET G.TanvtechId =a.NewTanvtechId 
FROM Tanvtech as G INNER JOIN @c as a ON a.TanvtechId =G.TanvtechId

Upvotes: 1

gbn
gbn

Reputation: 432271

From DBCC CHECKIDENT

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

If no rows have been inserted to the table since it was created, or 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.

So, this is expected for an empty or truncated table.

Upvotes: 56

Larry
Larry

Reputation: 21

I have the same problem, restoring from a backup after modifying the DB. I just add a dummy record and then delete it... then set RESEED to 0. Seems to work.

Upvotes: 2

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

This is logical, since you've changed (reseeded) the identity value to zero ?

DBCC CHECKIDENT (SyncSession, reseed, 1)

will reseed your identity column, and make sure that the first new record will start with 1.

Upvotes: 3

Related Questions