How to reset identity on SQL Server

When we create temporary table that contain row number on that table, we can create it by using int IDENTITY(1,1) – for iteration 1 step, here the scripts that we use :

drop table #tempTable

create table #tempTable
(  id int identity(1,1)
  ,name varchar(50)
  )

insert #tempTable
select 'John'

insert #tempTable
select 'Light'

insert #tempTable
select 'Lus'

delete #tempTable

insert #tempTable
select 'JJ'

insert #tempTable
select 'BB'

delete #tempTable
-- reset identity value on table
--DBCC CHECKIDENT (#tempTable, RESEED, 0)


insert #tempTable
select 'AA'

insert #tempTable
select 'DD'


select * from #tempTable

And the result like this :







If we use keyword : DBCC CHECKIDENT we can handle reset identity column value in SQL Server, here the script that we tried:

drop table #tempTable

create table #tempTable
(  id int identity(1,1)
  ,name varchar(50)
  )

insert #tempTable
select 'John'

insert #tempTable
select 'Light'

insert #tempTable
select 'Lus'

delete #tempTable

insert #tempTable
select 'JJ'

insert #tempTable
select 'BB'

delete #tempTable
-- reset identity value on table
DBCC CHECKIDENT (#tempTable, RESEED, 0)


insert #tempTable
select 'AA'

insert #tempTable
select 'DD'


select * from #tempTable
-- check identity lastposition
DBCC CHECKIDENT (#tempTable)

And the result will be right this :







Hope this information will help us to know SQL Server more.

Technorati Tags: Reset Identity Column Value in SQL Server,How to reset identity on SQL Server,Identity,Identity SQL Server