Resetting the identity Seed

Resetting the identity Seed

Speaking of the Identity field, there seems to be two very different camps. Some developers, such as the respected Joe celko, believe that the identity column forged the truth and introduce meaningless data to a database (based on its statement). Otherwise, it will be very compact. This article is especially in favor of the second Camp (I myself belong to this camp ). This camp holds that an object is almost the same as another object in the same domain (for example, an egg in a basket ). We believe that these objects should be assigned a meaningless primary key (PK ).

Assume that you depend on the identity column, which has a specified seed and step value, then occasionally you need to reset the identity value. This often happens because you delete many rows of data from the table and do not want the seed value to reflect millions of test records, it starts with 1 (or 10, 100, or any other number. But how do I reset the seed value after the test?

The solution is to run DBCC with the checkident keyword and specify appropriate parameters. If you have created a large number of data records during the test, passed the regression test, deleted all records, and want to reset the seed to 1, then you just need to simply issue a command similar to the following:

 

Use mydatabase
Go
DBCC checkident (mytable, reseed, 1)
Go

These rows of SQLCodeThe seed value of the specified table is forcibly reset to 1. However, you may not want to reset the seed to 1. In this case, you can replace the third parameter with the seed value you want.

Sometimes you may want to know the current seed, rather than resetting the seed. In this case, use the following SQL:

Use mydatabase
Go
DBCC checkident (mytable, noreseed)
Go

All in all, You can reset the seed value of any table to any value you want-except the value in use. You can test the database at any time, delete all test records, and reset the seed value as needed.