SQL find gaps
Posted on 2014-12-11
Hi found a solution for finding gaps in a squence of numbers. But it only Works for a small range.
Tried it for a sequence of 500 number and it worked great and fast.
But when I go up to 1500 the error comes
"An error occurred while executing batch. Error message is: There is not enough space on the disk."
Declare @StartNo BIgInt = 1111000
DECLARE @EndNo BigInt = 1111500
;with data as (
, row_number() over ( order by ObservationNo desc ) rx
where CompanyID = 2 and (ObservationNO >= @StartNo and ObservationNO <= @EndNo)
select tc.ObservationNO + 1 start_gap
, tn.ObservationNO - 1 end_gap
, row_number() over (order by tc.ObservationNO asc) rn
from data tc
left join data tn on tn.rx = tc.rx -1
where ( tn.ObservationNO <> tc.ObservationNO + 1 and tc.rx > 1 )
order by tc.rx desc
How do I solve this for large number sequences?