How to Reset the Identity Seed in SQL Server After Deleting Records?

Recovering SQL Server Identity Seed After Data Deletion

In SQL Server, the IDENTITY property automatically assigns unique integer values to new rows. Deleting rows can leave gaps in this sequence, breaking the continuous auto-increment. To fix this, use the DBCC CHECKIDENT command.

The DBCC CHECKIDENT Command

This command allows you to reset the identity seed value.

Syntax:

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

Example:

To reset the identity seed for a table named 'TestTable' to 0:

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

This restarts the counter from zero, guaranteeing consecutive integer values for new inserts.

Options Explained:

• NORESEED: Keeps the current identity seed.
• RESEED: Resets the seed. If new_reseed_value is omitted, it defaults to 0.
• WITH NO_INFOMSGS: Prevents informational messages from being displayed.

Azure SQL Database Compatibility:

The DBCC CHECKIDENT command is compatible with current Azure SQL Database versions, offering a reliable way to manage identity seeds and maintain data consistency.

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