Reset identity seed after deleting records in SQL Server

Question

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto-increment identity seed is set to “Yes”. This is done primarily because, in SQL Azure, each table has to have a primary key and identity defined.

But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.

How can I reset the identity column after I deleted the records so that the column has the sequence in ascending numerical order?

The identity column is not used as a foreign key anywhere in the database.

Answer 1

This documentation will help: click here

Run the following query:

DBCC CHECKIDENT ("your_database_with_table.table_name", RESEED, 100);

Here, Reseed is to reset the seed value and 100 is the value to which SQL Server will reset the seed.