CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Slimane Agouram
Slimane Agouram

Reputation: 365

SQL Server ID reseed after deleltion of a middle record

i have a Table from which i delete records . The problem is when i delete a certain record,its ID flies away too, so the ID order is no longer respected within the table. What i want is a SQL Server Procedure to rearrange records after the deletion of one of them.

Example :

ID                                              ID                     ID
1                                                1                      1
2  I delete record 2, i want to have this ===>   2  and NOT this :      3
3                                                3                      4
4                                                4                      5
5

Upvotes: 1

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You don't want to do this. The id should be a field that has no meaning other than identifying a row. You might have other tables that refer to the id and they would break.

Instead, just recalculate a sequential value when you query the table:

select t.*, row_number() over (order by id) as seqnum
from t;

Upvotes: 5

Related Questions