clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591633 fiddles created (45700 in the last week).

CREATE TABLE dbo.TableWithGaps(id int IDENTITY(1,1), x char(1)); INSERT dbo.TableWithGaps(x) SELECT TOP (50000) 'x' FROM sys.all_columns AS c CROSS APPLY (SELECT TOP (100) * FROM sys.all_objects) AS o; DELETE dbo.TableWithGaps WHERE id BETWEEN 11 AND 1000; -- 990 DELETE dbo.TableWithGaps WHERE id BETWEEN 10001 AND 17000; -- 7,000 DELETE dbo.TableWithGaps WHERE id BETWEEN 26001 AND 44000; -- 18,000
75990 rows affected
 hidden batch(es)


;WITH gaps AS ( SELECT id, next_id = LEAD(id, 1) OVER (ORDER BY id) FROM dbo.TableWithGaps ) SELECT gap_start = id + 1, gap_end = next_id - 1, gap_size = 1 + (next_id - 1) - (id + 1) FROM gaps WHERE next_id - id > 1 ORDER BY gap_size DESC;
gap_start gap_end gap_size
26001 44000 18000
10001 17000 7000
11 1000 990
 hidden batch(es)