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 sequence in ascending numerical order? The identity column is not used as a foreign key anywhere in database.

Accepted Answer

The `DBCC CHECKIDENT` management command is used to reset identity counter. The command syntax is:

```
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]
```

Example:

```
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO
```

It was not supported in previous versions of the Azure SQL Database but is supported now.

Thanks to Solomon Rutzky the docs for the command are now fixed.

Reset identity seed after deleting records in SQL Server

Solution

Problem