Friday, October 01, 2004

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:

2 Comments:

At 11:25 PM, Blogger Firedancer said...

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. :)

 
At 9:31 PM, Blogger Jonathan Yong said...

Good point. I have never try that with foreign key constraint.

 

Post a Comment

<< Home