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:
DBCC CHECKIDENT(myTable, RESEED,0)
the identity column correctly starts from 1.myTable_id
column: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.