SQL server identity column values start at 0 instead of 1
Solution 1
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.
Solution 2
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.
Solution 3
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.
Solution 4
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.
Solution 5
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
Estarius
Web and Windows Developer (.NET/C#), Graphics Designer Hobbies: playing guitar (and many other instruments i can get my hands on), snowboarding
Updated on July 09, 2022Comments
-
Estarius almost 2 years
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)
-
dburges almost 14 yearsThere is something wrong with your design if you are constantly reseeding the value. And why should it matter if it starts with 0 or 1? It's an autoincrement, it shouldn't matter what the value is just that it is unique and automatically assigned.
-
GeoffM about 9 yearsFive years late to the party but - like me - the OP could have just been developing and testing with a known set of data. Not necessarily anything wrong with the design.
-
nuander over 6 years@HLGEM - here's why it maters. if you are populating a code object from a database record, the object will initialize with an "ID" property of 0. Then if the populating is successful it will be something other than the default of 0. 0 can then indicate no record found or a "new" object.
-
-
David M about 15 yearsNo, that's not right. The first value used if you specify 1 in this way will be 2!
-
David M about 15 yearsAh, unless you do this on an empty table, in which case it takes the value you specify. Apologies!!!
-
Estarius about 15 yearsI've tried this on empty tables and now some tables start from 1 and some from 2.
-
James McMahon about 15 yearsJust FYI, the DELETE FROM statement will use the latter behavior, "the next row inserted uses new_reseed_value + the current increment value".
-
ZygD about 15 yearsDELETE will not reset seeds.. is that what you mean?
-
James McMahon about 15 years@GBN, that is true, but what I was referring to is using DBCC CHECKIDENT (SyncSession, reseed, new_reseed_value); to reset a seed for a table after a DELETE will take the new_reseed_value and add it the current increment value for the first row.
-
Damien over 14 yearsThis hasn't really answered the question. How do you ensure that the seed will always start at 1 - regardless of whether the table has been used or not?
-
ZygD over 14 years@Damien: It depends on what you have done previously. You can not guarantee because of the quote mentioned. You have know previous actions. Or explicitly TRUNCATE or rebuild table.
-
Ricardo Vigatti over 7 yearsthis is nuts! makes no sense to me, should behavior always the same way. omg microsoft...
-
howserss over 7 yearsleaving the value after reseed out will use the identity(a,b) value on the table currently. This was mentioned by a lower comment but seems to work best for this situation.
-
sushil.agarwal over 3 yearsHow I did, I have a Table tcsapplicable truncate table tcsapplicable go DBCC CHECKIDENT (tcsapplicable, RESEED, 0) go id Description 0 No 1 Yes On Sales 2 Yes On Receipt 3 Yes On Other
-
Sam over 3 yearsbut for an empty table (or delete all rows using TRUNCATE TABLE) will make the number starts from 0 instead of 1. How to make sure the autonumber always start from 1?
-
Keith over 3 years@Sam two ways, either pass the new seed value with the first example, or specify it in the identity column definition.
-
Sam over 3 yearsHi Keith, The column specified by
IDENTITY(1,1)
and I always calledDBCC CHECKIDENT (AspNetRoles, RESEED, 0);
beforeINSERT
statement. However, on the very first insert (ie. the database has just been created or delete all rows using TRUNCATE TABLE), the Id always 0. -
Keith over 3 years@Sam
DBCC CHECKIDENT (AspNetRoles, RESEED, 0)
will reset the identity to0
, useDBCC CHECKIDENT (AspNetRoles, RESEED)
to reset it to whatever the table definition states. -
4xMafole almost 3 yearsSaved my day, I was about to messy around searching on the net.
-
Gert Arnold over 2 yearsThis doesn't work for new/truncated tables. Even Redgate Data Compare uses this double DBCC CHECKIDENT "trick" to reseed tables in generated script so I couldn't believe it just does not suffice. I've found the only working solution to be this.