Rhyous

October 30, 2009

How to restart the AUTOINCREMENT number for a table in Microsoft SQL 2008?

Filed under: MS SQL Server — J. Abram barneck @ 2:37 pm

Ok, so I am in the middle of developing a database tool and so I populate a bunch of data, (by adding a bunch of rows), to a column that is AUTOINCREMENT.

So I have 3725 rows for a feature that is working. Now I am developing other features and debuggin them and so I want to reset the database to the same point it was before I started debugging.

So I am deleting all the rows above 3725, however my next AUTOINCREMENT number continues to go up.

I have this handy little SQL statement that should fix that:

To set the table back to 0.

DBCC CHECKIDENT (MyTable, RESEED, 0)

Now, if I set the table back to 0 that can be a problem if you still have rows in it.

The next row you try to insert will give you an error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Person’. Cannot insert duplicate key in object ‘dbo.Person’.
The statement has been terminated.

So if you have rows, try this:

DECLARE @size int
SET @size=(SELECT COUNT(*) From MyTable)
DBCC CHECKIDENT (MyTable, RESEED, @size)

Happy day, it works!!

Advertisements

2 Comments »

  1. Actually, this might cause a problem if you have deleted at least one row in your Person table. For instance, if your ID is up to 3725, but over the course of the app 25 records have been deleted somewhere in the table, the command SELECT COUNT(*) From MyTable will only return 3700. This will still cause a primary key violation when the next record attempts to insert an ID of 3701.

    What would be best is to delete your test records and then use SELECT MAX(ID_Column) From MyTable.

    Or, if at all possible, take a snapshot of the DB and work on that, then push your changes to the production db.

    Hope that helps!

    Comment by Matt Penner — May 17, 2010 @ 1:22 pm | Reply

    • Thanks for the tip. I will test and update the post when I get time.

      Comment by rhyous — May 17, 2010 @ 6:10 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: