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
DBCC CHECKIDENT (myTable, RESEED, 0)

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):

TRUNCATE TABLE myTable

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

Advertisements

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