First, read this:
http://stackoverflow.com/questions/4288089/reuse-identity-value-after-deleting-rows[
^].
Next, I'm afraid that using identity column is not a good direction for your scenario. If you need automatic numbering with reuse of deleted numbers, you can do, for example, the following:
1) Maintain "next row ID" counter by yourself -- you can have a table in the database that will hold the latest value of the ID counter.
2) Maintain a separate table of dropped IDs. You can populate it from an AFTER DELETE trigger on your data table.
3) When adding a new row, first try to "dequeue" lowest ID from the table of dropped IDs, then if unsuccessful, increment the ID counter (you can do it in an INSTEAD OF INSERT trigger).
There are two things to bear in mind with the above solution, though.
1) If there are multiple simultaneous INSERTs to data table, you have to correctly handle concurrency of incrementing the counter and dequeueing dropped IDs. If you use SQL Server 2005 and higher, you can use CTEs for this purpose (WITH...UPDATE and WITH...DELETE statements -- look here:
http://rusanu.com/2010/03/26/using-tables-as-queues/[
^]).
2) If your application produces a tough load by issuing miltiple simultaneous INSERTs and DELETEs on the data table, the dropped IDs table can become a performance bottleneck. This solution would work best if you have much less DELETEs than INSERTs.
Submit an article or tip