Number range Problems

First, the basic data is as follows:

Use tempdb;
Go
If object_id ('dbo. t1') is not null
Drop table DBO. T1
Go
Create Table DBO. T1 (col1 int not null primary key );
Insert into DBO. T1 (col1) values (1 );
Insert into DBO. T1 (col1) values (2 );
Insert into DBO. T1 (col1) values (3 );
Insert into DBO. T1 (col1) values (100 );
Insert into DBO. T1 (col1) values (101 );
Insert into DBO. T1 (col1) values (103 );
Insert into DBO. T1 (col1) values (104 );
Insert into DBO. T1 (col1) values (105 );
Insert into DBO. T1 (col1) values (106 );

 

Problem 1: locate the range of missing numbers. If the range is the same as the data above, the returned value is
4 ~ 99 102 ~ 102

One method:
Select col1 + 1 as start_range,
(Select Min (col1) from DBO. T1 as B
Where B. col1> A. col1)-1 as end_range
From DBO. T1 as
Where not exists
(Select * From DBO. T1 as B
Where B. col1 = A. col1 + 1)
And col1 <(select max (col1) from DBO. T1 );

 

Easier to understand:
Select cur + 1 as start_range, NXT-1 as end_range
From (select col1 as cur,
(Select Min (col1) from DBO. T1 as B
Where B. col1> A. col1) as NXT
From DBO. T1 as a) as d
Where NXT-cur> 1;

 

Question 2: locate all gaps (all missing numbers)
Select n
From DBO. Nums
Where n between (select Min (col1) from DBO. T1)
And (select max (col1) from DBO. T1)
And not exists (select * From DBO. T1 where col1 = N );

 

Problem 3: Find the existing range
Select min (col1) as start_range, max (col1) as end_range
From (select col1,
(Select Min (col1) from DBO. T1 as B
Where B. col1> = A. col1
And not exists
(Select * From DBO. T1 as C
Where B. col1 = C. col1-1) as GRP
From DBO. T1 as a) as d
Group by GRP;

 

Simple Method:
Select min (col1) as start_range, max (col1) as end_range
From (select col1, col1-row_number () over (order by col1) as GRP
From DBO. T1) as d
Group by GRP;