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.

DBCC CHECKIDENT ('Customer', RESEED, 50)

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

DBCC CHECKIDENT ('Customer', NORESEED)

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

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 *


+ 3 = 12

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>