How to restart the AUTOINCREMENT number for a table in Microsoft SQL 2008?
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!!
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!
Thanks for the tip. I will test and update the post when I get time.