SQL Server 表主鍵設計實戰：代理鍵 vs 業務複合鍵的效能、維護與分頁權衡

業務複合主鍵的連鎖副作用

典型主從表場景：

• 主表：銷售單（單據號 PK）
• 從表：銷售明細（單據號 + 商品碼 PK）
• 孫表：明細價格明細（單據號 + 商品碼 + 價格類型 PK）

四大痛點：

1. 資料冗餘：孫表需重複儲存單據號、商品碼，隨層級加深指數級膨脹
2. SQL 複雜度：多欄位 JOIN，ON t1.bill_no = t2.bill_no AND t1.item_cd = t2.item_cd AND t1.price_type = t2.price_type
3. 程式複雜度：傳遞多參數、構建複合鍵物件、序列化困難
4. 效能劣化：複合索引體積大、比較成本高、分頁查詢極難優化
5. Web 分頁困境：OFFSET/FETCH 或 ROW_NUMBER() 需穩定排序，複合鍵排序邏輯複雜且易發生重複/遺漏

解決方案：代理鍵

為每張表新增 無業務含義的數值型單欄主鍵（Surrogate Key / Proxy Key）。

實作範例

CREATE TABLE T_PK_DEMO (
  U_ID BIGINT NOT NULL,                    -- 代理鍵
  COL_OTHER VARCHAR(20) NOT NULL,          -- 業務欄位
  CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)
);

IDENTITY 自增列的限制與替代

IDENTITY 痛點：

• 使用者無法干預數值（匯入/合併/復原極不便）
• 發行/訂閱複寫需特殊處理
• 插入後才能取得值（需 SCOPE_IDENTITY()）

推薦：序列器模式 — 參考 Oracle Sequence，自建號碼發號表：

CREATE TABLE dbo.SeqGenerator (
  SeqName VARCHAR(50) PRIMARY KEY,
  NextVal BIGINT NOT NULL,
  IncrementVal INT NOT NULL DEFAULT 1
);

-- 取號存儲程序
CREATE PROC dbo.sp_GetNextSeq @SeqName VARCHAR(50), @NextVal BIGINT OUTPUT
AS
BEGIN
  UPDATE dbo.SeqGenerator SET @NextVal = NextVal, NextVal = NextVal + IncrementVal
  WHERE SeqName = @SeqName;
  IF @@ROWCOUNT = 0 RAISERROR('Sequence not found', 16, 1);
END

優勢：應用層可預先取號、支援分段分配（避免熱點）、跨資料庫合併無衝突、復原/匯入完全可控。

最佳實踐清單

1. 所有表預設加代理鍵：ID BIGINT PRIMARY KEY，叢集索引建於此欄（窄、遞增、靜態）
2. 業務鍵加唯一約束：UNIQUE (BillNo, ItemCd) 保證業務唯一性
3. 外鍵引用代理鍵：從表 ParentID BIGINT REFERENCES Parent(ID)
4. 分頁查詢直接用代理鍵排序：ORDER BY ID OFFSET @skip ROWS FETCH NEXT @page ROWS ONLY
5. 合併/匯入/同步場景：序列器模式優於 IDENTITY

例外：仍適合業務鍵為 PK 的場景

• 純查詢型查閱表（字典、代碼表），行數極少、極少異動
• 天然單欄業務鍵（如 ISO_Country_Code CHAR(2)）且極穩定
• 極度追求存儲空間、且確認無分頁/異動/合併需求