Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

SQL. How to find holes in sequences.

, 17 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Hello,Did 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

Hello,

Did 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; 

bye.
See you on

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Anton Burtsev
Web Developer
Russian Federation Russian Federation
I have started as a C++ developer in far 2000. I'd been developing banking and treasury software for 3 years using C++. In 2003 I switched to .NET at DELL. After that I worked as a project manager on different projects (internal audit, treasury automation, publishing house automation, etc.). Since 2009 I own a small software company specialized in SaaS services and develop a DotNetNuke modules.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberJörgen Sigvardsson28-Sep-10 1:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 17 Jun 2009
Article Copyright 2009 by Anton Burtsev
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid