how to reset identity column sequentially after deleting any row .

Question

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?

Sachin Singh · 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 Singh · Answer

@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 Setia · Answer

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 Setia · Answer

Try this : SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) AS RId, Name FROM MyTable

Aarti Gupta · Answer

@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 Setia · Answer

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.