How to Recalibrate an Auto-Increment Identity Column in SQL Server After Record Deletion?

Restoring Auto-Incrementing Identity Columns in SQL Server Following Record Deletion

Deleting rows from a table with an auto-incrementing identity column can disrupt the sequential numbering of subsequent inserts. To restore the proper ascending order, you need to reset the identity seed.

Using DBCC CHECKIDENT to Reset the Identity Seed

SQL Server offers the DBCC CHECKIDENT command for this task. The command's structure is:

<code class="language-sql">DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]</code>

For example, to reset the identity column in a table called 'TestTable', use this command:

<code class="language-sql">DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO</code>

This sets the identity seed back to 0, guaranteeing that new rows will receive consecutive index values.

Azure SQL Database Compatibility

While previously unsupported, the DBCC CHECKIDENT command is now available in Azure SQL Database and functions as described above.

The above is the detailed content of How to Recalibrate an Auto-Increment Identity Column in SQL Server After Record Deletion?. For more information, please follow other related articles on the PHP Chinese website!