If you just need to identify IF a list of numbers is in sequence, but not where the gaps are, I believe you can do it in a single pass of the table.  (Warning -- to understand this algorithm, some high school math is required!)

set nocount on

create table Numbers (id int not null)

insert Numbers values (6)

insert Numbers values (7)

insert Numbers values (8)

insert Numbers values (9)

insert Numbers values (10)

insert Numbers values (11)

insert Numbers values (12)

declare @Sum bigint, @Count bigint, @Min bigint

select @Sum = sum(convert(bigint, id)), @Count = count(*), @Min = min(id) from Numbers

declare @sqrt float

select @sqrt = sqrt( power((2*@Min - 1), 2) + 8*@Sum)

if @sqrt = floor(@sqrt) select 'Sequence' else select 'Not Sequence'

drop table Numbers

go

A self join solution:

select a.SeqNumber, max(b.SeqNumber)
from #SequenceTable a join #SequenceTable b on a.SeqNumber > b.SeqNumber
group by a.SeqNumber
having a.SeqNumber - max(b.SeqNumber) > 1

This way is a few hundred times faster (on large datasets)...NOTE:the details are missing but it still identifies the key holes in the sequence, except beiging and end which are obvious...?

SELECT s1.SeqNumber
FROM #SequenceTable s1
LEFT JOIN #SequenceTable s2
ON s1.SeqNumber = s2.SeqNumber -1
WHERE s2.SeqNumber IS NULL

I agree, the join on "where Seq2.SeqNumber < Seq1.SeqNumber" will be a killer with large tables.

This version uses a simple a = b-1 join to find the gaps, then a subquery to find the last value before the gap.  On 1 million rows it ran in 1/4 the elapsed time and 1/13 the CPU time of the original in the article.

Thanks all for the great examples of other ways to get the same results.  It is interesting to see these different methods and the understandings of how SQL works under the hood so to speak, with different timings on the various methods.  For me timing was not a big issue, and my 31 second run over a table of 3.5 million rows seemed fair.  Timing becomes more significant if a process is being repetatively and frequently run. 

Over my dataset, Scott Coleman’s method ran in 25 seconds, being significantly slower than his own reported difference, indicating other factors come into play.

Brendan Smith’s method ran in 10 seconds, and although I accept this is faster, it does not do the computes for the additional rows in my output set, so is not a reliable comparison, but looks promising.

Mike Gress’s example did not complete, I bombed it off at over 14 minutes with no results (sorry Mike).

I didn’t try Hanslindgren’s method as it is not my database, so I was unable to add indexes to it. 

To Mike Arney, my high school maths was never that good, but I am sure there will be some that can work this one out from your example.

Joe Celko’s second method also did not complete, and I bombed this off at over 14 minutes with no results, and Joe’s first method took less than a second and produced no results (sorry Joe).

Looking deeper at my source dataset, the timing differences I get to your own results may be due to the extreme number of gaps.  My data set produced 1.1 million rows (of identified gaps).  Also within my data set are sequence number duplications, which may have contributed to the failure of some of the scripts.

Thanks you all for your feedback and suggestions.

Stephen

Hi Stephen!

Thank you, it is not always you get such an exhausting feedback of how things turn out when you post replies and hope you can help.

Hanslindgren