Resetting the identity column on a SQL Server table

Just a quick post. Resetting the identity value of a SQL Server table is pretty straight forward. There are two ways to do it. Note – THESE OPERATIONS DELETE THE DATA IN THE TABLE.

The way I do it is:

DELETE myTable

Note that value above for the call to CHECKIDENT equals 0. The result is the first insert will have the value 1. So the RESEED value is 1 less than where you want the numbering to start.

The second way (for completeness):


This second method is often futile as it can’t be performed on a table that takes part in a relationship.


Leave a Reply

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

You are commenting using your 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