IDENTITY 不保證連續 以及 視窗函數（Window Functions）

 這篇 2017 年的筆記在核心觀念上非常正確，即便到了 2025 年，IDENTITY 不保證連續 以及 視窗函數（Window Functions）是解決此類問題的最佳解 這兩點依然是 SQL Server 的黃金準則。

不過，經過了 8 年的演進（SQL Server 2019, 2022 以及 Azure SQL 的普及），我們可以用更現代的視角來補充細節與最佳實踐。

以下是用 2025 年的技術狀態 對這篇筆記的重新解釋與建議：

---

1. 核心觀念驗證：為什麼 2025 年還是會有跳號？

筆記中提到「IDENTITY 保證唯一，不保證連續」，這點在 2025 年依然是鐵律。跳號的原因除了 2017 年提到的 交易退回 (Rollback) 之外，在現代雲端與高並發環境下，還有一個更常見的原因：

• 快取 (Cache) 機制與伺服器重啟：

  現代版本的 SQL Server（尤其是 2012 之後及 Azure SQL Database），為了提升效能，預設會對 IDENTITY 進行快取（Cache）。如果伺服器意外重啟或服務切換（Failover），記憶體中未使用的號碼就會丟失。這導致你不只會看到跳 1、2 號，有時會看到跳 1000 號 或 10000 號 的斷層。

  • 2025 註解：這是為了高併發效能所做的權衡，通常被視為「特性」而非 Bug。

2. 效能評比更新：誰是冠軍？

2017 年的筆記比較了三種方法，結論是 LAG 函數成本最低 (18%)。

在 2025 年，這場比賽已經不用比了。

• Self Join (自我連結)：已被視為過時的寫法（Legacy pattern）。

• OFFSET：雖然可用，但語法較冗長，可讀性不如視窗函數。

• LAG / LEAD (視窗函數)：依然是 絕對的霸主。

現代 SQL Server 的優化器（Query Optimizer）對於視窗函數的優化已經非常成熟。使用 LAG 或 LEAD 來解決「Gaps and Islands」（間隙與孤島）問題是標準的 Best Practice。

3. 2025 年的最佳實作程式碼 (Modern T-SQL)

原本 2017 年的 LAG 範例寫得不錯，但我們可以讓它更精簡且資訊更豐富（不僅找出哪裡跳號，還能算出跳了幾號，以及缺少的號碼範圍）。

推薦寫法：

SQL

-- 2025 Modern Approach
WITH GapAnalysis AS (
    SELECT 
        c1 AS CurrentID,
        -- LEAD 往後看一筆，直接與下一筆比較，比 LAG 往前看更直觀一點
        LEAD(c1) OVER (ORDER BY c1) AS NextID 
    FROM tblNum
)
SELECT 
    CurrentID + 1 AS GapStart,       -- 缺號開始
    NextID - 1 AS GapEnd,            -- 缺號結束
    (NextID - CurrentID) - 1 AS GapCount -- 缺了幾個號碼
FROM GapAnalysis
WHERE NextID - CurrentID > 1; -- 只要下一號減現在這號大於1，代表中間有洞

為什麼這個寫法更好？

1. 使用 LEAD：邏輯上我們常想著「這一號跟下一號有沒有連著」，LEAD 語意上更符合直覺（當然用 LAG 效果完全一樣）。

2. 明確指出缺口範圍：直接告訴你「缺了 6 到 7 號」，而不只是「5 號後面有斷掉」。

4. 架構層面的建議 (Architectural Advice in 2025)

如果你的業務需求非常在意「連續編號」（例如：統一發票、會計傳票），不要依賴 IDENTITY。

在 2025 年的系統設計建議如下：

1. 接受跳號 (Accept Gaps)：

   對於訂單編號、會員 ID 等，跳號通常是可以接受的。為了追求連續號碼而犧牲資料庫的高併發寫入效能（需要 Table Lock）是不划算的。

2. 如果必須連續，請自建序號表 (Custom Sequence Table)：

   如果法律規定必須連續，請建立一張獨立的表格來管理序號，並在交易中使用 UPDLOCK 來確保取號。這會犧牲效能，但能保證連續。

3. 考慮使用 SEQUENCE 物件：

   SQL Server 2012 引入了 CREATE SEQUENCE 物件。雖然它預設也有快取導致的跳號問題，但它提供了 NO CACHE 的選項（雖然會降低效能），且可以跨多張 Table 共用同一個序號池，比 IDENTITY 更有彈性。

總結

這篇 2017 的筆記結論在 2025 年依然正確。

• 結論：使用 視窗函數 (LAG / LEAD) 是尋找不連續資料的唯一正解。

• 效能：依然是最高的。

• 心態調整：除非是會計與法規需求，否則在雲端與微服務架構當道的今天，「視跳號為常態」 是更健康的系統設計思維。