FindMissingNumbers.sql

/*
Purpose:
- Find gaps between the minimum and maximum numeric key in a table.

Safety:
- Read-only. Large key ranges can be expensive; narrow the range when appropriate.

Requirements:
- SQL Server 2012 or later.

Customization:
- Replace dbo.SampleRecords and record_id.
*/

DECLARE @MinimumValue BIGINT;
DECLARE @MaximumValue BIGINT;

SELECT
    @MinimumValue = MIN(record_id),
    @MaximumValue = MAX(record_id)
FROM dbo.SampleRecords;

;WITH Numbers AS
(
    SELECT @MinimumValue AS value
    WHERE @MinimumValue IS NOT NULL

    UNION ALL

    SELECT value + 1
    FROM Numbers
    WHERE value < @MaximumValue
)
SELECT n.value AS missing_value
FROM Numbers AS n
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.SampleRecords AS r
    WHERE r.record_id = n.value
)
ORDER BY n.value
OPTION (MAXRECURSION 0);