Have you ever been asked to find all holes in document numbers (docs are sequentially numbered)? I had to do this search twice. Once for auditing purposes: holes may mean some sort of forgery. And now customers want to reuse numbers of deleted docs. And how do you think holes can be found using SQL? Assume we have:
create table tbl (id int identity primary key);
So, there is nothing easier than intersect tab to itself with incremented id.
select id+1 from tbl
except
select id from tbl;
This query returns all holes. The result is list of missed id ranges begins. Ends of missed id ranges can be got with similar query.
select id-1 from tbl
except
select id from tbl;
I also need the function for the minimum free doc number. This is it:
select top 1 * from
(
select id+1 as id from tbl
except
select id from tbl
) t;
See you on Codeproject.