Unexpected behavior with DBCC checkident when reseeding an identity column in a table
If you are using a flavor of Sql Server, and have tables with identity columns, you may have run into a need to reseed the table. You can reseed the identity using the DBCC checkident TSQL command.
You need to be aware that this command might behave differently depending on if the table has had data in the past.
- If the table has never had any rows, or all rows were truncated, the first row inserted after using this command uses the value passed into the command as the identity.
- Otherwise, (at least on SQL Server 2016 SP1), the first row inserted will use the value passed into the command plus 1 (presumably the “increment” value of the identity column).
This behavior is partially documented, but it seems more like they are documenting a bug, rather then a feature, which they do not want to fix because it would break existing scripts.
The following script shows how it behaves, when a table has had data, and when it has never had data.
If for some reason you need to reseed a table that is empty, but may or may not have had data in the past, you can use something like the following that gives you the behavior you would expect:
With this approach, regardless if anything has ever been in the table, the next record inserted will have an identity of 1.