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

user_1232087 0
Viewed 15187

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

dbcc sql-server-2012 tsql sql-server
4 Answers

There is nothing bad with the answer here but the confusion comes from Microsoft approach itself.

I think that:

DBCC CHECKIDENT(myTable, RESEED, 0)

Should have the same behavior everywhere:

  1. on new created table,
  2. after delete table records,
  3. after truncating the table

Otherwise we need to check the table status before running this.

user_44957410

This does the trick for me:

  DBCC CHECKIDENT ([Table], RESEED, 0)
  DBCC CHECKIDENT ([Table], RESEED)
user_44917680
Related