Click here to Skip to main content
13,247,336 members (83,182 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


8 bookmarked
Posted 17 Jun 2009

SQL: How to Find Holes in Sequences

, 17 Jun 2009
Rate this:
Please Sign up or sign in to vote.
How to find hole in sequences in SQL

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 
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 
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 
   select id from tbl 
) t; 

See you on .


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


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.

You may also be interested in...

Comments and Discussions

QuestionMy vote of 1 Pin
digimanus21-Apr-16 0:55
memberdigimanus21-Apr-16 0:55 
GeneralMy vote of 5 Pin
Jörgen Sigvardsson28-Sep-10 1:58
memberJörgen Sigvardsson28-Sep-10 1:58 

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

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

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