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.