SQL Server Identity column
The value of a identity column is always increased even after you remove all records in that table. Here are some tips how you can manipulate the seed value for identity column.
To remove all record in the table and reset the seed to its initial value, run :
TRUNCATE TABLE YourTableName
To find out seed info about the identity column, run :
DBCC CHECKIDENT (YourTableName, NORESEED)
To set the seed value to a particular value (Eg: The seed value is currently 25, so the next value to be insert into the column is 26, but you want to it to be 31), run :
DBCC CHECKIDENT (YourTableName, RESEED, 30)
Note : If you specify the new seed valus to N, the next value to be insert will be N + 1.
Labels: sql
2 Comments:
Just to add that your can't TRUNCATE a table if you have FOREIGN KEY constraints defined. You will first have to drop those constraints before being able to issue TRUNCATE. :)
Good point. I have never try that with foreign key constraint.
Post a Comment
<< Home