SQL Server 資料表主鍵設計五大陷阱與最佳實踐：代理鍵替代業務鍵

現狀：業務鍵作主鍵的連鎖副作用

傳統設計以業務邏輯鍵 (單據號、單據號+商品碼、多欄位聯合鍵) 作主鍵，導致：

• 資料冗餘：主從表層層遞傳主鍵欄位，儲存量膨脹。
• SQL 複雜：多欄位 JOIN、多參數傳遞、動態 SQL 拼接。
• 效能損耗：聯合索引寬、比較成本高、統計資料維護重。
• 分頁困難：Web 分頁需處理複合排序、游標分頁邏輯複雜。

解決方案：引入代理鍵

為每張表新增一個與業務無關的數值型代理鍵 (Surrogate Key) 作為主鍵；業務唯一性改由 UNIQUE 約束/索引 保證。

推薦定義 (非 IDENTITY，參考 Oracle Sequence)

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？

• 使用者無法干預值 (無法指定、無法更新)。
• 發行/訂閱合併複雜 (身分值衝突)。
• 部分資料還原時身分值斷層難處理。
• 插入後才能取得值 (需 SCOPE_IDENTITY() / OUTPUT 子句)。

自製 Sequence 表模擬 Oracle 序列

CREATE TABLE T_DB_SEQ (
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_OWNER VARCHAR(50) NOT NULL DEFAULT 'DBO',
    SEQ_CURRENT BIGINT NOT NULL DEFAULT 0,
    SEQ_MIN BIGINT NOT NULL DEFAULT 0,
    SEQ_MAX BIGINT NOT NULL DEFAULT 9223372036854775807,
    SEQ_STEP INT NOT NULL DEFAULT 1,
    IF_CYCLE INT NOT NULL DEFAULT 0,  -- 0=不循環, 1=循環
    CONSTRAINT PK_T_DB_SEQ PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);

核心函數介面

• F_create_SEQ(@seq_name)：建立序列 (寫入 T_DB_SEQ)。
• F_GET_NEXT_SEQ_VAL(@seq_name)：取得下一值 (含並發鎖定保護)。
• F_GET_SEQ_CUR_VAL、F_SET_SEQ_VAL、F_DEL_SEQ、F_SEQ_EXISTS 等輔助函數。

主從表關聯新模式

-- 父表
CREATE TABLE T_PK_DEMO (
    U_ID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    COL_OTHER VARCHAR(20) NOT NULL
);

-- 子表：自身代理鍵 PK + 父表代理鍵 FK
CREATE TABLE T_PK_DEMO_C (
    U_ID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    COL_OTHER VARCHAR(20) NOT NULL,
    P_ID BIGINT NOT NULL,
    CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID)
        REFERENCES T_PK_DEMO(U_ID) ON DELETE CASCADE
);

業務唯一性如何保證？

代理鍵不承擔業務語義，需額外加約束：

-- 方式 1：資料庫層 UNIQUE 約束 (推薦)
ALTER TABLE T_PK_DEMO ADD CONSTRAINT UQ_T_PK_DEMO_ColOther UNIQUE NONCLUSTERED (COL_OTHER);

-- 方式 2：唯一非叢集索引
CREATE UNIQUE INDEX IX_T_PK_DEMO_ColOther ON T_PK_DEMO (COL_OTHER);

-- 方式 3：應用層檢查 (不推薦為唯一手段，易有競態條件)

為何主鍵索引用 NONCLUSTERED？

• 代理鍵不更新、無重複、無範圍查詢需求，不需叢集索引有序特性。
• 叢集索引每表僅一個，極其寶貴，應留給範圍查詢頻繁、排序需求高、窄且遞增的業務欄位 (如 OrderDate、CreatedTime)。

實戰收益：Web 分頁查詢優化

採用代理鍵 + 叢集索引置於時間欄位，分頁查詢可直接利用叢集索引有序特性：

-- 高效分頁 (SQL 2012+ OFFSET/FETCH 或 CTE ROW_NUMBER)
SELECT * FROM Orders
WHERE CreatedTime >= @StartDate
ORDER BY CreatedTime DESC
OFFSET @PageSize * (@PageNo-1) ROWS FETCH NEXT @PageSize ROWS ONLY;

避免了傳統「雙重排序 + NOT IN」或「游標」的低效寫法。

代理鍵非銀彈，但能將「業務識別」與「關聯效能」解耦，是大型系統主鍵設計的工業標準做法。