#### Topic: How to reveal holes in a range of numbers

1) I specify a range of numbers for check in presence of "holes" in a column
2) I need to reveal all holes and to write down value:
With which gaps began
Number on which ended
Amount of the passed
And so on all holes in  a range
The column is a lot of
select
t1.f1,
min (t2.f1) as mins;
min (t2.f1)-t1.f1-1 as counts
from #temp1 t1, #temp1 t2
where t2.f1&gt; t1.f1
group by t1.f1
having t1.f1+1 &lt;min (t2.f1)
This request solves a problem but brakes on big  information (to compare for time it is necessary to 10-20 million lines)
Can at  there is other decision, will be glad for the help

#### Re: How to reveal holes in a range of numbers

500 thousand  time here at a forum it was considered

#### Re: How to reveal holes in a range of numbers

Make the indexed mullions-strong list and find a difference of two dial-ups.

#### Re: How to reveal holes in a range of numbers

you mean to create the similar table with the same range of numbers (only without passes) and to compare to the original?

#### Re: How to reveal holes in a range of numbers

If the version is not specified, it is supposed that implementation can be on any version of the server.

``````create table #temp (id int);
insert into #temp (id)
values (1), (2), (4), (8);
select prev_id + 1 as gap_start, t.id - 1 as gap_end, t.id - t.prev_id - 1 as gap_count
from (select id, lag (id) over (order by id) as prev_id
from #temp) as t
where t.id - t.prev_id&gt; 1;``````

#### Re: How to reveal holes in a range of numbers

Minamoto;
Thanks like that that are necessary

#### Re: How to reveal holes in a range of numbers

kolyady, for the sake of justice it not the best variant, look here at comparing on productivity (from the link which to you above a distance, found):
https://blog.waynesheffield.com/wayne/a … detection/