SQL Server | Reset Identity Column

When you create a table in SQL Server, you would also have an Identity Primary Key. This type of key would increase as new rows are added. But sometimes you would just need to delete all the rows and start all over again.

To restart the identity column, you could delete the column and re-add it again. This would normally work, but if you have relations with the primary key, you wouldn’t be allowed to delete it.

The next code that will make this possible, and restart the Primary Key from 0, thus the new row will be set as 1:

DBCC CHECKIDENT(‘table_name’, RESEED, 0)

If you want to reset to any other number, just replace 0 with the chosen number reduced by 1. So to start the Primary Key from 1000, use the next SQL Code:

DBCC CHECKIDENT(‘table_name’, RESEED, 999)

Hope this Helps 😉

Resources: here


No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *