Reseeding a SQL Table

I came across a problem recently in which I needed to reseed a SQL table to a certain number for the auto incrementing of the primary key.

You need to know the following :

  • tableName – The name of the table you are reseeding
  • Reseed or NoReseed – Will discuss briefly after the example.
  • Int – The number you want to start the records off with, subtracted by one.

DBCC CHECKIDENT (tableName,reseed, Int)

An example with Jobs as your table name and you would like to reseed starting at 10 :

DBCC CHECKIDENT (Jobs,reseed, 9)

Now say you just deleted all records from a table and you need to start the identity column over at 1. Notice above we set the reseed int one less than the desired value. This means when we delete everything from a table and reseed, we’ll have to use zero.

--Reseeding to start the increment at 1
DBCC CHECKIDENT (Jobs, reseed, 0)

Reseed or NoReseed

Reseed – Choose this option if you would like to set the auto-incrementing primary key to a certain number.

NoReseed – Choose this option to return the current identity value and the maximum identity value from the specified table.  As the name implies, the table will not be changed and the identity value will remain the same.

If you would like to read more, go to ‘s MSDN article which can be found here about reseeding tables.

Releated Post

Leave a Reply

%d bloggers like this: