SQL Server 查找連續序列中缺失的最小編號：集合差集法一行搞定

業務場景

表 tt 主鍵 ID 為連續整數，因刪除操作出現空洞。新增記錄時需復用最小缺失編號。

核心 SQL

SELECT MIN(t.ID) AS MissingID
FROM (SELECT ID + 1 AS ID FROM tt) t
WHERE t.ID NOT IN (SELECT ID FROM tt);

原理解析

1. 子查詢 (SELECT ID + 1 AS ID FROM tt) 生成「每個現有 ID 的下一個數」。
2. WHERE ... NOT IN (SELECT ID FROM tt) 過濾掉已存在的數，剩下的即為「緊跟在現有 ID 後但自身不存在」的數。
3. MIN() 取最小者，即全局最小缺失編號。

示例驗證

CREATE TABLE tt (ID INT PRIMARY KEY);
INSERT INTO tt VALUES (1),(2),(3),(5),(6),(10);
-- 缺失：4,7,8,9 → 最小 4

SELECT MIN(t.ID) FROM (SELECT ID+1 AS ID FROM tt) t
WHERE t.ID NOT IN (SELECT ID FROM tt); -- 返回 4

進階：獲取前 N 個缺失編號

SELECT TOP 5 t.ID
FROM (SELECT ID + 1 AS ID FROM tt) t
WHERE t.ID NOT IN (SELECT ID FROM tt)
ORDER BY t.ID;

大表性能優化

• 確保 ID 有聚集索引（主鍵默認）。
• 改用 NOT EXISTS 通常比 NOT IN 更穩定（避免 NULL 陷阱）：
  WHERE NOT EXISTS (SELECT 1 FROM tt WHERE ID = t.ID)
• 極大表可考慮 ROW_NUMBER() OVER (ORDER BY ID) AS Rn 配合 WHERE ID <> Rn 找第一個不匹配處。