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

Question

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:

Solution 1

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.

Solution 2

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.

Solution 3

Works as expected see also
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql#examples

The value 1 means that the current identity will be at 1 and the next identity will start on 2

To get it starting on 1 you should do

DBCC CHECKIDENT(myTable, RESEED, 0)