Hello,
I've deleted some records from a table in a Sql Server db. Now the Ids go from 101 to 1200. I want to delete the records again, but I want the id's to go back to 102. Is there a way to do this in Sql Server?
+9
A:
Issue the following command to reseed mytable to start at 1:
DBCC CHECKIDENT (mytable, RESEED, 1)
Read about it in the Books on Line (BOL, SQL help). Also be careful that you don't have records higher than the seed you are setting.
Robert Wagner
2009-02-04 05:05:24
... because the ids of these records will be happily be reused again, causing a bad mess.
nalply
2010-09-17 10:16:10
A:
I figured it out. It's DBCC CHECKIDENT ('tablename', RESEED, newseed)
jumbojs
2009-02-04 05:12:06
A:
You do not want to do this in general. Reseed can create data integrity problems. It is really only for use on development systems where you are wiping out all test data and starting over. It should not be used on a production system in case all related records have not been deleted (not every table that should be in a foreign key relationship is!). You can create a mess doing this and especially if you mean to do it on a regular basis after every delete. It is a bad idea to worry about gaps in you identity field values.
HLGEM
2009-02-05 19:09:57