how to reset identity column sequentially after deleting any row .

Aarti Gupta

I have a table like shown below

Id	Name
1	Aarti
2	Kriti
3	Priya

if Kriti (2) got deleted then all her reputation earned is given to Priya (3) and all the rows below are promoted that is the table now becomes like

Id	Name
1	Aarti
2	Priya
3	Rashmi
4	Nancy

How to achieve the same in SQL server?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
At first this is a very horrible idea to reset identity of all users, yet this can be achieved but includes lots of unnecessary calls to dabase which will slow down the performance.
Better don't do that but if you still want this then here is the solution .
CREATE procedure [dbo].[spDelete]
@Id int=null
as
begin
delete from StudentTest where id =@Id

create table #helpTmpStudentTest(id int, [name] varchar(50));

insert into #helpTmpStudentTest
select row_number() over (order by Id), [name] from StudentTest



begin tran
begin try

    delete from StudentTest

    set identity_insert StudentTest ON

    insert into StudentTest (Id, [name])
    select id,name from #helpTmpStudentTest

    set identity_insert StudentTest OFF

    commit tran
end try
begin catch
    rollback tran
end catch


drop table #helpTmpStudentTest
end
Sachin Singh5y ago
@Varun
I have already mentioned that this includes lots of unnecessary calls to databse and slows down the performance,
Actually Reseting an Identity column of all rows is a bad idea in itself.
But if she want that then this is the solution, not the optimized one just a solution. If you can optimized it then you are very welcome.
Varun Setia5y ago
Only by returning records from select query you can use Row_number based id instead of actually touching the original id to solve your problem
Varun Setia5y ago
Try this : SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS RId, Name FROM MyTable
Aarti Gupta5y ago
@Varun you are right,
but if you have any better solution then please post the solution. I don't think there is any other solution than what @sachin Singh has proposed. Cause I just don't want only select, the Id of the person decides their achievements to date and if any row is deleted then his achievement is given to the previous one.
Varun Setia5y ago
You need an optimised solution to work with records exceeding 1 lakh rows. You can select all records with row_number based on order by id. You must not update large number of rows on every delete.