find_gaps_in_seq.sql

WITH SourceData AS
(
    SELECT
        [MyColumn] AS ID, -- 1. The column containing the sequence
        RowNum = ROW_NUMBER() OVER (ORDER BY [MyColumn]) 
    FROM
        dbo.[MyTable] -- 2. The table containing the column
),
Ranked AS
(
    SELECT
        *,
        DENSE_RANK() OVER (ORDER BY ID - RowNum) As Series 
    FROM SourceData
),
Counted AS
(
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY Series) AS SCount
    FROM Ranked
),
Gaps AS
(
    SELECT
        MinID = MIN(ID), 
        MaxID = MAX(ID), 
        Series
    FROM
        Counted
    GROUP BY Series
)

SELECT
    FirstNumberInGap = (a.MaxID + 1),
    LastNumberInGap = (b.MinID - 1),
    GapSize = ((b.MinID - 1) - a.MaxID)
FROM
    Gaps a
INNER JOIN
    Gaps b ON a.Series + 1 = b.Series
ORDER BY
    FirstNumberInGap