Mini Post: Reseed Identity Column

Hey all, when in doubt: Mini Post!

In this post we look at reseeding the identity column via T-SQL.

Please note that the rules change depending on if there are already values in your table when you attempt the reseeding.

1) With Values in Table

Suppose you want the next value in your table (Table1) to be 12. We will use the following:

DBCC CHECKIDENT (Table1, reseed, 11)

Note that your reseed value will be one less than the value you want to appear (e.g. 11 is one less than 12).

2) Without Values in Table (Empty Table)

Suppose you have an empty table and you want the next value to be 12 (for whatever reason). We will use the following:

DBCC CHECKIDENT (Table1, reseed, 12)

Note that your reseed value will be exactly the value you want to appear (e.g. we use 12).

3) Reseed via TRUNCATE

If you are going to delete all the values in a table anyway before you reseed, you can use the TRUNCATE statement instead of DELETE, and the table will automatically reseed from whatever it's initial starting value was.

TRUNCATE TABLE Table1

(Please read up on TRUNCATE before you decide that it's a viable solution for your needs)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)