How to Reset Identity Value in SQL Server

In SQL Server, identity columns are used to automatically generate unique numeric values, commonly for primary key columns. However, situations may arise, such as after deleting records or resetting test data, where you need to reset the identity value so that new records start with a specific number again.

If you've ever deleted all the rows from a table but noticed that the identity column keeps incrementing from the last used value, this post will show you how to reset it using the DBCC CHECKIDENT command.

👉 Original post: How to Reset Identity Value

What is DBCC CHECKIDENT?

SQL Server provides the A DBCC CHECKIDENT command to manually reseed (reset) the identity value of a table. This is particularly useful after large deletions or when reloading data during development or ETL processes.

Syntax:

DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue);

• 'TableName': Your table name.
• RESEED: Instructs SQL Server to change the identity value.
• NewSeedValue: The number you want the next identity value to start from.

Example Scenario

Suppose you have a table called Employee With an identity column ID.

To reset the identity to start from 1 after deleting all records:

DELETE FROM Employee;
DBCC CHECKIDENT ('Employee', RESEED, 0);

⚠️ Note: If you reseed to 0, the next row inserted will receive ID = 1 (since identity starts from the seed + 1).

How to Check the Current Identity Value

To see the current identity seed without making changes:

DBCC CHECKIDENT ('Employee', NORESEED);

This will return the current seed and the next value to be used.

Important Considerations

• TRUNCATE vs DELETE: Using TRUNCATE TABLE resets the identity automatically, but DELETE does not.
• Existing Records: Be careful when reseeding if the table still contains rows—setting the seed too low can cause primary key violations.
• Permissions: Running DBCC CHECKIDENT may require ALTER permissions on the table.

When Should You Reset Identity Values?

• After deleting test or demo data in development environments.
• During staging or ETL operations, when reloading data.
• When maintaining a consistent identity sequence is necessary for reports or integrations.

Resetting identity values is a simple but powerful feature in SQL Server. With DBCC CHECKIDENT, you can take control over how identity values are managed in your tables.

For more details, you can refer to the following links:

Reassign the Identity of All Tables

The table has Identity Columns