DBCC CHECKIDENT(myTable, RESEED,1) reseeding from 2

nam picture nam · Aug 10, 2017 · Viewed 10.4k times · Source

In SQL Server 2012, the following query is seeding the identity column myTable_id from 2 instead of 1. Why? myTable_id is also PK.

DELETE FROM myTable;
GO
SELECT * FROM myTable --0 rows are returned as expected
GO
DBCC CHECKIDENT(myTable, RESEED,1)
GO
INSERT INTO myTable(col1,col2,col3) SELECT FROM AnotherTable(col1,col2,col3)
GO
SELECT * FROM myTable --1005 rows are returned as expected, but identity value starts from 2
GO

Remark:

  1. The data inserted is right, the only issue is that the newly inserted data starts from 2 instead of 1.
  2. In the above sql code if I use DBCC CHECKIDENT(myTable, RESEED,0) the identity column correctly starts from 1.
  3. Following is snapshot in SSMS for the myTable_id column:

enter image description here

Answer

DavidG picture DavidG · Aug 10, 2017

From the docs:

The seed value is the value inserted into an identity column for the very first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

So if you seed from 10, the next value to be inserted will be 11.