Reset Identity Seed after Deleting Records in SQL Server

In SQL Server, when you delete records from a table with an identity column, the identity seed value is not automatically reset. This means that if you insert new records, they will start from the next available identity value, rather than from 1. To reset the identity seed, you need to use the `DBCC CHECKIDENT` command.

Syntax

The basic syntax for resetting the identity seed is:

DBCC CHECKIDENT ('table_name', RESEED, new_seed_value); 

Where:

• `table_name` is the name of the table that contains the identity column.
• `new_seed_value` is the new seed value you want to set.

Example

Let's say we have a table called `Employees` with an identity column `EmployeeID`, and we want to reset the identity seed to 1 after deleting all records:

DELETE FROM Employees; DBCC CHECKIDENT ('Employees', RESEED, 1); 

After executing these commands, the `EmployeeID` column will be reset to start from 1 for new inserts.

Example with a Specific Seed Value

You can also specify a specific seed value other than 1:

DELETE FROM Employees; DBCC CHECKIDENT ('Employees', RESEED, 100); 

In this example, the `EmployeeID` column will be reset to start from 100 for new inserts.

Important Notes

Before resetting the identity seed, make sure to:

• Take a backup of the database to ensure data safety.
• Verify that you have deleted all records from the table.
• Test the identity column to ensure it is reset correctly.

Alternative Method using TRUNCATE TABLE

Another way to reset the identity seed is to use the `TRUNCATE TABLE` command, which not only deletes all records but also resets the identity seed:

TRUNCATE TABLE Employees; 

This method is more efficient than deleting records and then resetting the identity seed, but it requires more permissions and can only be used on tables without foreign key constraints.