Reseting Identity value back to previous value or 1 after Delete command in SQL

Reseting Identity value to previous value or 1 after Delete command in SQL

In order to explain this first you should know like if  a table having Identity value column assuming that the number of records are 5 and you deleted 5th record and inserted a new record then the next value in Identity key column will be 6 not 5.It will be 1,2,3,4,6

secondly if you will delete all data from the table then when you will insert a new record in the table, it will starts from 7 whereas if you use truncate command to delete all data then insert a new record then it will starts from 1 this is one of the big difference between truncate and Delete commands
.

Now i will explain how to reset identity value back to previous value or 1.

--Creating table with identity column

CREATE TABLE [dbo].[Tableidentity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL
) ON [PRIMARY]
GO

--Inserting some values

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2'),('value3')

GO

select * from tableidentity

ID value

1 value1    

2 value2    

3      value3

Now we will delete a record from table

delete from tableidentity where id in (2,3)

 insert value 2 once again

INSERT INTO [Tableidentity]

VALUES ('value2')

GO

select * from tableidentity

ID value

1 value1    

4 value2   

you will notice that now Id value of new record is 4 as, 2 and 3 id value has been deleted but you want that it should be 2 i.e.  same as previous value so remember as soon as you will delete a record reseed the value to its previous value.

delete from tableidentity where id =4

after deleting reseeding it back to previos values

-- identity column reset to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)

Now insert a new record 

INSERT INTO [Tableidentity]

VALUES ('value2')

GO

ID value

1 value1    

2 value2  

It will insert as required order.

Note: when you are deleteting all records of table using delete command then new value inserted will be maximum Identity value of deleted value+1

for eg we will delete all record and then will insert a new record.

--deleting all records of table

delete  from [Tableidentity]

Now insert a new record 

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2')

ID value

3 value1   

4      value2

The values are inserting from 3 as i mentioned above the maximum identity value when deleted was 2 so new records started from 3

So it's a best practise to use truncate when deleting all records so that you may not have to reseed it to 1.

--truncate command

truncate table tableidentity.

--Reseed to 1 without truncate i.e. after running delete all record command setting identity value back to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)