missing_indices.sql

missing indices

-- Assuming table t has one row with lower_bound and upper_bound
WITH Numbers AS (
    -- Anchor member: start with the lower bound
    SELECT lower_bound AS number
    FROM t
    UNION ALL
    -- Recursive member: increment the number by 1 each time
    SELECT number + 1
    FROM Numbers
    WHERE number + 1 <= (SELECT upper_bound FROM t)
)
-- Select numbers not present in the index column
SELECT number
FROM Numbers
WHERE number NOT IN (SELECT [index] FROM t)
OPTION (MAXRECURSION 0); -- Allows the CTE to recurse beyond the default 100 levels