Binaryworld - How to reset Identity of Autonumber column? ... [ SQL Server -> T-SQL ]

There are two ways to reset identity field

1) Use TRUNCATE TABLE rather than DELETE. This is a non-logged operation, however, and may not be what you need.

2) Use the DBCC CHECKIDENT command to reseed your identity value:

DBCC CHECKIDENT('mytable', RESEED, 0) -- next row inserted will use IDENTITY value 1

following example will clerify more.

Click here to copy the following block

if (select object_id('#a') ) IS NOT NULL
drop table #a

declare @new_reseed_value int

create table #a
(f1 int IDENTITY NOT NULL,
f2 varchar(50))

INSERT INTO #a VALUES ('AAA')
INSERT INTO #a VALUES ('BBB')
INSERT INTO #a VALUES ('CCC')
INSERT INTO #a VALUES ('DDD')
INSERT INTO #a VALUES ('EEE')
INSERT INTO #a VALUES ('FFF')

select * from #a --//

delete from #a where f1>=5

INSERT INTO #a VALUES ('EEE')
INSERT INTO #a VALUES ('FFF')

select * from #a --//insert without reset

delete from #a where f1>=5

set @new_reseed_value=(select max(f1) from #a)
DBCC CHECKIDENT (#a, RESEED,@new_reseed_value)

INSERT INTO #a VALUES ('EEE')
INSERT INTO #a VALUES ('FFF')

select * from #a --//insert with reset