Sometimes you have tables that have an identity column (SQL Server) and whatever reason you need to reseed (reset) the identity column values. This may be a result of inserting and deleting a lot of data during testing a process. It might also come up if you want to start the sequence at a different number or you backed up data and want to start it over again (like in a new year). You could create a new table and then import from the original into the new without the identity column, however; this is not necessary and a lot of extra work.

There is a way to do it without having to create, insert, drop.

For example if you want to reseed a customer table starting with 50 then your syntax would be.

[code language=”sql”]
DBCC CHECKIDENT (‘Customer’, RESEED, 50)
[/code]

If just want to know what the next number is then your syntax would be.

[code language=”sql”]
DBCC CHECKIDENT (‘Customer’, NORESEED)
[/code]

As you can see that whatever your seed number is the next record inserted will be the seed + 1 so in the above example the new number would be 51.

Word of Caution: It is not a good idea to reseed an identity column in a table that contains data without validating the impact of doing it.