Reset Sql Server Identity Column Without Truncating
The Sql Server madness continues at work. Here’s a little tip on how to reset the identity column of a table if its set to increment by one for each row thats inserted, without truncating the entire table.
If you run the following command at the sql prompt, you can reset the identity field:
DBCC CHECKIDENT(‘myTableName’, RESEED, 0)
This will set the identity field to 0 on the table ‘myTableName’. One caveat with this is that if you have rows in that table, it will allow you to reseed the table to an existing ID number. For instance, if you have a Users table, with a UserID auto number field, and there is a User with UserID = 10, you can reseed the table to 9, and Sql Server will let you. However, when you try to insert another row, you’re going to get an error because the UserID 10 already exists.
Anonymous said,
Wrote on January 14, 2006 @ 11:46 pm
Not exactly — you’ll only receive an error if you have a unique or primary key contraint on the column. The IDENTITY property does not guarantee uniqueness, and either by resetting the seed using DBCC CHECKIDENT or using IDENTITY_INSERT, you can insert duplicate values.