Common Table Expressions (CTE’s) are a new structure in SQL Server 2005 that can be used to assist with performing recursive operations. The following example demonstrates how a CTE can be used to find the gaps in a range.
Consider the following table:
CREATE TABLE nums
(
col1 INT NOT NULL
)
INSERT nums SELECT 1
INSERT nums SELECT 2
INSERT nums SELECT 3
INSERT nums SELECT 5
INSERT nums SELECT 6
INSERT nums SELECT 9
As can be seen 4, 7 and 8 are missing from the range sequence.
The following query can be used to identify the first gap in the range ie. 4 and 7:
SELECT col1 + 1
FROM nums T1
WHERE NOT EXISTS
(SELECT 1
FROM nums T2
WHERE T2.col1 = T1.col1 + 1)
AND col1 < (SELECT MAX(col1) FROM nums)
However to determine all the missing numbers in the range is significantly more complex. However this process can be simplified using a CTE as illustrated below:
DECLARE @i INT
SELECT @i = MAX(col1) FROM nums;
WITH tmp (GapCol1) AS
(
SELECT DISTINCT a.col1 + 1
FROM nums a
WHERE NOT EXISTS
(SELECT 1 FROM nums b
WHERE b.col1 = a.col1 + 1)
AND a.col1 < @i
UNION ALL
SELECT a.GapCol1 + 1
FROM tmp a
WHERE NOT EXISTS
(SELECT 1
FROM nums b
WHERE b.col1 = a.GapCol1 + 1)
AND a.GapCol1 < @i
)
SELECT GapCol1
FROM tmp
ORDER BY GapCol1;
