Some time ago, I was helping one of my junior developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons, he was compelled to delete few rows from the table. On inserting new rows in the table, he noticed that the rows started from the next identity value which created a gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.
The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is a missing identity and then we will move on to the solution.
Developers can easily deter the above issue by avoiding a gap in sequence of identity column through two additional SQL tricks of reseeding identity. We will now see the same example with the solution to the above gap issue. On deleting records, the table was reseeded with identity, which was deleted.
You can download the complete SQL script here.
I hope this solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.
- 8th April, 2009: Initial post