Reseeding a SQL Table

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.reseed

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.

Tags: | Posted under ASP .NET 4.0 | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *