SQL Server 2017

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


(No column name)


Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) 
 Jun 15 2019 00:45:05 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table messages(id int, type int, msg varchar(3));
insert into messages
values
  (1, 83, 'AAA'),
  (2, 84, 'BBB'),
  (3, 0, 'CCC'),
  (4, 83, 'DDD'),
  (5, 84, 'EEE'),
  (6, 83, 'FFF'),
  (7, 83, 'GGG'),
  (8, 84, 'HHH'),
  (9, 84, 'III'),
  (10, 0, 'JJJ'),
  (11, 84, 'KKK');
11 rows affected

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when gaps <> 0 then 'Gap detected'
            when next_type = 84 then msg + next_msg 
            else msg end
from
(
  select
    m.type, lead(m.type) over(order by m.id) as next_type,
    m.msg, lead(m.msg) over(order by m.id) as next_msg,
    l.id02 - l.id01 - count(*) over () - 1 as gaps
  from messages m
  join limits l on l.id01 < m.id and l.id02 > m.id
) firstbulk
where type = 83;


(No column name)


DDDEEE

FFF

GGGHHH


delete from messages where id = 9;
1 rows affected

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when gaps <> 0 then 'Gap detected'
            when next_type = 84 then msg + next_msg 
            else msg end
from
(
  select
    m.type, lead(m.type) over(order by m.id) as next_type,
    m.msg, lead(m.msg) over(order by m.id) as next_msg,
    l.id02 - l.id01 - count(*) over () - 1 as gaps
  from messages m
  join limits l on l.id01 < m.id and l.id02 > m.id
) firstbulk
where type = 83;


(No column name)


Gap detected

Gap detected

Gap detected