AddToAny
Tue, 06/15/2010 - 18:05
I want to reset an identity coloumn in SqlServer. I am using
Delete from "TableName".
It is deleteing all data in the table but it is not resetting my Identity value.
I have tried Truncate Table , But I will not be able to restore my data back in case of truncate.
I need a method to delete all rows from the table and simultaneosly reset the identity value.
You can use DBCC CHECKIDENT function to do this.
eg I want to reseed a table
DBCC CHECKIDENT('MyTableName',RESEED,4)
Now the identity value will start from 5. So first value that you will see on insertion is 5.
You need to be carefull while using it as it throws exception if the column where you are using it is a primary key column and if any value already exists that identity is trying to insert. If this column is not marked as primary key or unique key column than duplicate value will get inserted.
See this
Other way, you can use alter command to alter identity column
ALTER TABLE Persons IDENTITY(100,1)