Renewed Auto Number Column Value In SQL Server 2008
In this article I will tell you how to reset the identity column in table.
In this article I will tell you how to reset the identity column in table. Sometimes we store dummy data while developing and testing an application. But after completion of testing phase, we need to reset the identity column in a table.
Lets see an example to reset the IDENTITY column.
Create table
use person
create table reseed_identity
(
id int identity (1,1),
name varchar(50)
)
Insert some values in table
Some methods used to reset the identity value are given below.
Method 1
To reset whole table starting from 0, write following code snippet:
TRUNCATE TABLE reseed_identity
DBCC CHECKIDENT (reseed_identity,RESEED, 0)
This will delete the whole data from table and reset the identity column value to 0.
Method 2
To start numbering the identity column with a arbitrary number, use following steps:
Delete some data from table. Suppose I delete id from 3 to 6.
Reset the auto number using DBCC CHECKIDENT command.
Now insert the values in table. The value of the identity column of the next record inserted will be 4.
