SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);
ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);
INSERT INTO GapsIslands
SELECT 1,  1 UNION ALL 
SELECT 1,  2 UNION ALL 
SELECT 1,  5 UNION ALL 
SELECT 1,  6 UNION ALL 
SELECT 1,  8 UNION ALL 
SELECT 1,  9 UNION ALL 
SELECT 1, 10 UNION ALL 
SELECT 1, 12 UNION ALL 
SELECT 1, 20 UNION ALL 
SELECT 1, 21 UNION ALL 
SELECT 1, 25 UNION ALL 
SELECT 1, 26;
12 rows affected

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
, CASE WHEN EXISTS ( SELECT  * 
                     FROM GapsIslands AS b
                     WHERE b.ID = a.ID 
                       AND b.SeqNo = a.SeqNo - 1 )
       THEN 'Exists'
       ELSE 'Not exists' 
       END AS previous_SeqNo_exists
FROM GapsIslands AS a
ORDER BY SeqNo


ID
SeqNo
RowNum
previous_SeqNo_exists


1
1
1
Not exists

1
2
2
Exists

1
5
3
Not exists

1
6
4
Exists

1
8
5
Not exists

1
9
6
Exists

1
10
7
Exists

1
12
8
Not exists

1
20
9
Not exists

1
21
10
Exists

1
25
11
Not exists

1
26
12
Exists


SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS ( 
    SELECT  * 
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)


ID
SeqNo
RowNum


1
1
1

1
5
2

1
8
3

1
12
4

1
20
5

1
25
6

ID	SeqNo	RowNum	previous_SeqNo_exists
1	1	1	Not exists
1	2	2	Exists
1	5	3	Not exists
1	6	4	Exists
1	8	5	Not exists
1	9	6	Exists
1	10	7	Exists
1	12	8	Not exists
1	20	9	Not exists
1	21	10	Exists
1	25	11	Not exists
1	26	12	Exists