How to Reset the SQL Server Identity Seed After Deleting Rows?

Resetting SQL Server Identity Seed After Row Deletion

SQL Server tables with auto-incrementing primary keys utilize an identity seed—the initial value for the identity column—which increases with each new record. This ensures uniqueness and ascending order within the identity column.

Deleting records, however, can disrupt this sequence, potentially causing the automatically generated index column to become misaligned. This is especially problematic if the identity column serves as a foreign key elsewhere.

Restoring the identity seed after deletions is achieved using the DBCC CHECKIDENT command.

This command resets the identity counter for a specified table. The command's syntax is:

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

For instance, resetting the TestTable table's identity column to 0:

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

Important Note: While previously unsupported in older Azure SQL Database versions, DBCC CHECKIDENT is now fully functional.

Persistent issues with identity seed resets may necessitate contacting Microsoft support.

The above is the detailed content of How to Reset the SQL Server Identity Seed After Deleting Rows?. For more information, please follow other related articles on the PHP Chinese website!