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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s