如何在SQL Server中查询Article表的未使用ID列表

算力阿峰

2026-8-13

在SQL Server中查询Article表未使用的可用ID列表

我们的Article表中id字段为numeric(5,0)，取值范围是1到99999，需要找出所有未被使用的ID，以下是两种可行的实现方案：

方案一：生成全量ID后筛选未使用项

通过递归CTE生成1到99999的所有可能ID，再与Article表左连接，筛选出无匹配记录的ID：

WITH AllPossibleIDs AS (
    SELECT 1 AS ID
    UNION ALL
    SELECT ID + 1
    FROM AllPossibleIDs
    WHERE ID < 99999
)
SELECT ap.ID AS AvailableID
FROM AllPossibleIDs ap
LEFT JOIN Article a ON ap.ID = a.id
WHERE a.id IS NULL
OPTION (MAXRECURSION 0); -- 递归次数超过默认100，需开启此选项

方案二：基于现有ID的间隙生成可用ID

先找出现有ID之间的空缺区间，再生成区间内的所有ID，适合ID分布较密集的场景，性能更优：

WITH ExistingIDs AS (
    SELECT id FROM Article
    UNION ALL
    SELECT 0 -- 处理从1开始的空缺
    UNION ALL
    SELECT 100000 -- 处理最大ID到99999的空缺
),
IDGaps AS (
    SELECT 
        curr.id + 1 AS StartGap,
        next.id - 1 AS EndGap
    FROM ExistingIDs curr
    JOIN ExistingIDs next ON next.id > curr.id
    WHERE NOT EXISTS (
        SELECT 1 FROM ExistingIDs e WHERE e.id > curr.id AND e.id < next.id
    )
    AND curr.id + 1 <= next.id - 1
),
AvailableIDs AS (
    SELECT StartGap AS ID
    FROM IDGaps
    UNION ALL
    SELECT ID + 1
    FROM AvailableIDs a
    JOIN IDGaps g ON a.ID >= g.StartGap AND a.ID < g.EndGap
)
SELECT ID AS AvailableID
FROM AvailableIDs
ORDER BY ID
OPTION (MAXRECURSION 0);