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!!