Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Finding Gaps in a Sequential Number Sequence

By Stephen Lasham,


The process of adding and more specifically deleting records from the database over time results in a large number of gaps in tables’ identifier sequence number. It may be desirable to know where the breaks are, perhaps to re-use the numbers following identification of duplicates that need renumbering or where a bulk insert is required, or perhaps to identify gaps in a cheque sequence, or analyze potential issues with the database programs creating gaps invalidly. To do this however you must first identify the gaps.

To identify the gaps I have created the following script, which using the sample temp table created below demonstrates one method for achieving the desired result.

Sample table creation

Create table #SequenceTable
(	SeqNumber Int

Declare @Seq int

Set @Seq = 10

while @Seq <= 100
	Insert into #SequenceTable values(@Seq)
	Set @Seq = @Seq + 1

Insert into #SequenceTable values(101)
Insert into #SequenceTable values(102)
Insert into #SequenceTable values(103)
Insert into #SequenceTable values(105)
Insert into #SequenceTable values(106)
Insert into #SequenceTable values(107)
Insert into #SequenceTable values(114)
Insert into #SequenceTable values(115)
Insert into #SequenceTable values(116)
Insert into #SequenceTable values(129)
Insert into #SequenceTable values(130)
Insert into #SequenceTable values(131)

Gap identification code

Select LastSeqNumber
	, NextSeqNumber
	, FirstAvailable = LastSeqNumber + 1
	, LastAvailable = NextSeqNumber - 1
	, NumbersAvailable = NextSeqNumber - (LastSeqNumber + 1) 
 from (
  Select LastSeqNumber = (Select isnull(Max(Seq2.SeqNumber),0) as SeqNumber 
   from #SequenceTable Seq2
   where Seq2.SeqNumber < Seq1.SeqNumber) 
  , NextSeqNumber = SeqNumber
    from #SequenceTable Seq1
  ) as A
 where NextSeqNumber - LastSeqNumber > 1
 order by LastSeqNumber

Resulting output


























A detailed breakdown

The code requires us to access table #SequenceTable twice.

The first is a straight read of table #SequenceTable (Seq1), setting NextSeqNumber to the number found in each row’s SeqNumber field. NextSeqNumber is the first SeqNumber value following a break.

The second sub query select on table #SequenceTable (Seq2), selects a single value for field LastSeqNumber, being the highest value found that precedes the value loaded into NextSeqNumber. LastSeqNumber is the immediate number found preceding a break.

Finally, wrapping the selection of LastSeqNumber and NextSeqNumber in an outer select statement (A), selects only records where a gap exists (NextSeqNumber – LastSeqNumber > 1). You can change the value here to find only gaps big enough for the number of rows you require.

The code performs a small trick when determining LastSeqNumber to ensure capture of a break at the start of the number sequence, in this case values 1 to 9. Here it uses isnull to return a zero value instead of null when it tries to find a value less than the smallest found number. Of course, when the smallest number is one, then the difference between zero and one is one, so this will not report a gap. For this to work, the SeqNumber column must not contain numbers less than zero.

As a final nicety, the code simply calculates the First available number and last available number in the missing number range and the number of available slots in the sequence this represents.


The above code is a simple method for identifying gaps in a number sequence. My own specific use was to find gaps for re-use when renumbering duplicate sequenced records. For this purpose, I was able to change the gap qualifier > 1 on the outer query, to the size of the gap that I required to fill with additional rows. I originally thought a poor conversion from an earlier database created the gaps, however on running the script on several occasions I saw new gaps appearing. It turned out that code was inadvertently corrupting sequence numbers causing gaps, and I arranged a fix.

For your own purpose, it should be easy to insert your own table and column names using change/replace, and make this work over your own tables.

Total article views: 13222 | Views in the last 30 days: 20
Related Articles

Inserting Multiple Records Using Range Numbers

Inserting Multiple Records Using Range Numbers


Insert rows



Insert Error: Column name or number of supplied values does not match table definition.

Insert Error: Column name or number of supplied values does not match table definition.


bulk insert creating numbers like "3.65257e+006"

the number is actually 3652571


Finding Gaps in a Sequential Number Sequence

Comments posted to this topic are about the content posted at

sql puzzles    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones