Find Gaps in Sequential Numbers in TSQL

To find gaps in ID numbers in a table using T-SQL, you can use a self-join or a common table expression (CTE) along with the LEAD or LAG window functions. Here’s an example using the LEAD function to identify gaps in sequential ID numbers:

Assuming you have a table named YourTable with an ID column and you want to find gaps between consecutive ID numbers:

WITH Gaps AS (
    SELECT
        ID,
        LEAD(ID) OVER (ORDER BY ID) AS NextID
    FROM
        YourTable
)
SELECT
    ID AS GapStart,
    NextID - 1 AS GapEnd
FROM
    Gaps
WHERE
    NextID - ID > 1;

In this example, the CTE named Gaps fetches the ID column along with the next ID using the LEAD function. The final query then selects rows where the difference between the next ID and the current ID is greater than 1, indicating a gap in the ID sequence.

If you want to find gaps using a self-join, here’s how you can do it:

SELECT
    t1.ID AS GapStart,
    MIN(t2.ID) - 1 AS GapEnd
FROM
    YourTable t1
LEFT JOIN
    YourTable t2 ON t1.ID < t2.ID
GROUP BY
    t1.ID
HAVING
    MIN(t2.ID) - t1.ID > 1;

In this self-join approach, the query compares each ID with all other IDs in the table and identifies where there’s a gap.

Both approaches should yield similar results and help you identify gaps in sequential ID numbers within your table. Choose the one that suits your preferences and the complexity of your use case.