Get a list of gaps in a sequence in SQL Server
If you need to find gaps in a sequence (for example, gaps in an ID column, or in a sort order column, etc) you can use the below query.
It uses a series of Common Table Expressions to slice up the gaps between islands in the range and presents a list of each gap.
I am using this to find gaps in id numbers in a table.
The list is inclusive, so all of the ids presented are “IN THE GAP” and ids that DO exist NEVER appear in the list. It also shows the number of ids “IN THE GAP”.
-- Uses a number of common table expressions to obtain gaps in a sequence -- There are only two changes you need to make, annotated in the first CTE -- 1. The column containing the sequence -- 2. The table containing the column WITH SourceData AS ( SELECT YourIdColumn AS ID, -- 1. The column containing the sequence RowNum = ROW_NUMBER() OVER (ORDER BY OrgId) FROM dbo.YourTableName -- 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
Written by Steve Fenton on