SQL Server Performance Forum – Threads Archive

identity coloumn

Hello everybody, I’ve got a table which contains 20,000 records. In this table i’ve the one column of Identity type which stores record numbers(1,2…n). More records are expected to add/delete. Now the problem is, if I delete the 100th record, identity coloumn should be updated by decrementing their values. (ex. I’ve total records of 20,000. I delted the one record of 100th. then,101th record should be updated to 100 and so on.
Thanking you with anticipation
Rajendar
ok

---

I don’t think an identity column would be the best type if you want to do this sort of thing. I’ve never come accross this kind of requirement before but I reckon if you’re looking for a long term solution you might want to consider having some kind of batch program run every couple of hours to update your ids (and not have your ids as an identity column). Reorganising your ids on the fly would be quite heavy on the server. Cheers
Shaun World Domination Through Superior Software

---

I agree with Shaun. If this is what you need to implement than IDENTITY is not your solution. It is something you will have to manage yourself. Before getting into a solution, a few questions.
Could you explain better your need of this seq. number?
Are there other table with a reference to this number that would need to be updated as well?
Would you really need to update all the numbers following the one deleted or could you just move the last one in it’s place? How often data will change? Bambola.

---

]]>