CODEWITHSUNDEEP
sqlsql-servert-sql
vhie
vhie

Reputation: 252

Reset existing data ids and identity after deleting data?

I have deleted some data in our database and I just want to know if it is possible to kind of reset the auto-increment value and reset also the id of the existing data that I have not deleted.

Say I have a user table that has about 100 data and I deleted data from 1-50. Is it possible to reset the id to start from 1 again and the auto-increment to start to 51 since that will be the next number?

I know about DBCC CHECKIDENT (mytable, RESEED, 0). But if my understanding is correct it will only reset the next value of id of the next data that will be inserted in the table. What I want is to reset the existing ids as well.

Please let me know if this is possible.

NOTE

I understand that this is not really advisable to do. I am also aware that this might cause serious concern later on but I really would just like to know if it is possible. I would also keep in mind your advice and am grateful for it. Thank you

Upvotes: 1

Views: 2319

Answers (2)

gotqn
gotqn

Reputation: 43646

Don't waste time for doing this. The ID is for internal use. Also, if you have relations with other tables you need to update the IDs there, too.

You can create a view or just add in any SELECT statement ROW_ID() OVER(ORDER BY [id]) and you will get an increment value without gaps.

Upvotes: 3

TomC
TomC

Reputation: 2814

If you want to fill in the gaps, you will have to do it manually, using Set identity_insert on.

For example, if I want to change my rows 51-60 to be 1-10:

set identity insert mytable on
insert mytable(id, column1, column2)
select id-50, column1, column2 
from myTable 
where id between 51 and 60
set identity_insert mytable off
delete mytable where id between 51 and 60

Then reseed your ID and it should be good to go.

Upvotes: 0

Related Questions