SQL Server Gaps

 in SQL Server  tagged sequence / gap / gaps / lead by

What are gaps? Gaps and islands are classic challenges in T-SQL that involves a sequence of values. The gaps task involves identifying the range of missing values.

The easiest way to get started with this is to create a table with one column of integers and show a couple of queries that identify the gaps in the sequence of integers. We will start with creating a table called Gaps1. We will iuse the INSERT INTO statement to populate it with a small amount of data. We will also create a primary key on the table. You can run the following code in a non-production database, or just create a new database on a non-production machine. This discussion is based on the discussion found on pages 291 to 291 of Itzik Ben-Gan’s et al Microsoft Book called T-SQL Querying. 

SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Gaps1', 'U') IS NOT NULL DROP TABLE dbo.Gaps1;
CREATE TABLE dbo.Gaps1(col1 INT NOT NULL CONSTRAINT PK_Gaps1 PRIMARY KEY);
GO
INSERT INTO dbo.Gaps1(col1) VALUES(1),(2),(3),(7),(8),(9),(11),(15),(16),(17),(28);

Get pairs. 

-- Cur - Next pairs
SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
FROM dbo.Gaps1;

Below is the solution query, and below that are the results as a screen shot of SSMS. 

WITH C AS
(
  SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
  FROM dbo.Gaps1
)
SELECT cur + 1 AS range_from, nxt - 1 AS range_to
FROM C
WHERE nxt - cur > 1;

No Primary Key

Let’s do the same thing against a table that does not have a primary key defined. 

IF OBJECT_ID('dbo.GapsNoPK1', 'U') IS NOT NULL DROP TABLE dbo.GapsNoPK1;
CREATE TABLE dbo.GapsNoPK1(col1 INT NOT NULL);
GO
INSERT INTO dbo.GapsNoPK1(col1) VALUES(1),(2),(3),(7),(8),(9),(11),(15),(16),(17),(28);

We get exactly the same results.

No PK and Un-sequenced

What happens when the data is not sequenced? Let’s switch the 7 and the 11 and see what happens. 

IF OBJECT_ID('dbo.GapsNoPKUnSeq1', 'U') IS NOT NULL DROP TABLE dbo.GapsNoPKUnSeq1;
CREATE TABLE dbo.GapsNoPKUnSeq1(col1 INT NOT NULL);
GO
INSERT INTO dbo.GapsNoPKUnSeq1(col1) VALUES(1),(2),(3),(11),(8),(9),(7),(15),(16),(17),(28);

The following query returns the same results in the results pane of SSMS. 

WITH C AS
(
  SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
  FROM dbo.GapsNoPKUnSeq1
)
SELECT cur + 1 AS range_from, nxt - 1 AS range_to
FROM C
WHERE nxt - cur > 1;