SQL Server 自增 ID 不連續缺口查找：三種高效能 T-SQL 方案對比

問題背景

含 `IDENTITY(1,1)` 欄位的表，因交易回滾、刪除、插入失敗、伺服器重啟 (SQL 2012+ 預設快取 1000 個值) 導致 ID 出現缺口。需求：找出 **缺口前後的邊界記錄** (如 3→5 缺口，找出 ID=3 和 ID=5) 或 **所有缺失的 ID 值**。

測試資料建立

CREATE TABLE t_IDNotContinuous (
    ID INT IDENTITY(1,1) NOT NULL,
    ValueString NCHAR(10) NULL
);
SET IDENTITY_INSERT t_IDNotContinuous ON;
INSERT INTO t_IDNotContinuous (ID, ValueString) VALUES
(1, 'test'), (2, 'test'), (3, 'test'), (5, 'test'),
(6, 'test'), (7, 'test'), (10, 'test');
SET IDENTITY_INSERT t_IDNotContinuous OFF;
-- 缺口：3→5 (缺 4)、7→10 (缺 8,9)

方案一：LEAD/LAG 視窗函數 (SQL 2012+、最簡潔、效能最佳)

WITH Gaps AS (
    SELECT
        ID,
        LEAD(ID) OVER (ORDER BY ID) AS NextID
    FROM t_IDNotContinuous
)
SELECT ID AS GapStart, NextID AS GapEnd,
       NextID - ID - 1 AS MissingCount
FROM Gaps
WHERE NextID - ID > 1;

結果

GapStart GapEnd MissingCount
3        5      1   -- 缺 4
7        10     2   -- 缺 8,9

方案二：相關子查詢 / CROSS APPLY (SQL 2005+、相容性好)

SELECT
    t1.ID AS GapStart,
    (SELECT MIN(t2.ID) FROM t_IDNotContinuous t2 WHERE t2.ID > t1.ID) AS GapEnd
FROM t_IDNotContinuous t1
WHERE (SELECT MIN(t2.ID) FROM t_IDNotContinuous t2 WHERE t2.ID > t1.ID) - t1.ID > 1;

或使用 `CROSS APPLY` (可讀性更好)：

SELECT t1.ID AS GapStart, ca.NextID AS GapEnd
FROM t_IDNotContinuous t1
CROSS APPLY (
    SELECT MIN(ID) AS NextID FROM t_IDNotContinuous WHERE ID > t1.ID
) ca
WHERE ca.NextID - t1.ID > 1;

方案三：數字表 / 遞歸 CTE 列出所有缺失 ID (需完整列舉)

-- 1. 遞歸 CTE 產生連續數字 (小範圍適用)
WITH Nums AS (
    SELECT MIN(ID) AS n FROM t_IDNotContinuous
    UNION ALL
    SELECT n+1 FROM Nums WHERE n < (SELECT MAX(ID) FROM t_IDNotContinuous)
),
Missing AS (
    SELECT n FROM Nums
    EXCEPT
    SELECT ID FROM t_IDNotContinuous
)
SELECT n AS MissingID FROM Missing
OPTION (MAXRECURSION 0);

大範圍建議使用**永久數字表** (Tally Table) 或 `sys.objects` 衍生：

-- 衍生 1~1,000,000 連續數字
WITH N AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects o1 CROSS JOIN sys.objects o2
)
SELECT n AS MissingID
FROM N
WHERE n BETWEEN (SELECT MIN(ID) FROM t_IDNotContinuous) AND (SELECT MAX(ID) FROM t_IDNotContinuous)
EXCEPT
SELECT ID FROM t_IDNotContinuous;

效能對比 (百萬列測試環境)

進階：找出缺口前後的完整記錄 (含業務欄位)

WITH Gaps AS (
    SELECT ID, LEAD(ID) OVER (ORDER BY ID) AS NextID
    FROM t_IDNotContinuous
    WHERE LEAD(ID) OVER (ORDER BY ID) - ID > 1
)
SELECT t.*
FROM t_IDNotContinuous t
JOIN Gaps g ON t.ID BETWEEN g.ID AND g.NextID
ORDER BY t.ID;

預防與監控建議

• **監控缺口率**：`SELECT 1.0 * (MAX(ID) - COUNT(*)) / MAX(ID) FROM Table;` 超過 5% 建議調查。
• **避免頻繁刪除/回滾**：改用軟刪除 (`IsDeleted BIT`)、批次插入驗證後再提交。
• **SQL 2017+ 序列物件**：`CREATE SEQUENCE` 可設定 `NO CACHE` 減少重啟缺口 (但效能稍降)。
• **備份/還原一致性**：還原後 `DBCC CHECKIDENT ('Table', RESEED)` 確保種子正確。