Renewed Auto Number Column Value In SQL Server 2008

In this article I will tell you how to reset the identity column in table.
  • 3487

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

ResetIdentityColumn01.jpg

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.

ResetIdentityColumn02.jpg 

Reset the auto number using DBCC CHECKIDENT command.

 ResetIdentityColumn03.jpg

Now insert the values in table. The value of the identity column of the next record inserted will be 4.

 ResetIdentityColumn04.jpg

© 2020 DotNetHeaven. All rights reserved.