Romil N Romil N - 4 years ago 358
SQL Question

Reset Identity Seed after deleting records in SQL Server

I have insert records in the SQL Server database table. The table had primary key defined and an auto increment identity seed is set to YES. This is done primarly because in SQL Azure, each table has to have an primary key and Identity Defined.

But since I now to have delete some records from the table, the identity seed for those table will be disturbed and hence the Index column (which is autogenerated with an increment of 1) will get disturbed.

Therefore, kindly let me know how can reset the Identity column after I delete the records so that the column has sequence in ascending numerical order.

P.S. : The Identity column is not used as a FK anywhere in database.

Answer Source

The DBCC CHECKIDENT management command is used to reset identity counter. However it's not supported by SQL Azure.

Example: DBCC CHECKIDENT ('[TestTable]', RESEED, 0) GO

A possible workaround is a SET IDENTITY_INSERT statement which allowes you to insert specific values into identity column.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download