SQL Server 主鍵設計最佳實踐：拒絕業務鍵，擁抱代理鍵

業務主鍵的連鎖災難

傳統設計常以業務欄位（單號、商品碼）或多欄位組合作為主鍵，隨著主從表層級加深，引發四大問題：

1. 資料冗餘：子表必須重複儲存父表所有主鍵欄位
2. SQL 複雜化：JOIN 條件冗長，多欄位比對
3. 程式複雜化：傳遞多參數、DTO 膨脹
4. 效能下降：索引膨脹、判斷條件增加、分頁查詢極難優化

解決方案：代理鍵

為每個資料表新增一個無業務含義的數值型主鍵（如 U_ID BIGINT），作為唯一識別碼；業務唯一性改由 UNIQUE 約束/索引 保證。

建表範例

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),
    CONSTRAINT UQ_T_PK_DEMO UNIQUE NONCLUSTERED (COL_OTHER) -- 業務唯一性
);

為何不使用 IDENTITY 自增列？

自製序列器：參考 Oracle Sequence 設計

序列管理表

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 0,
    SEQ_STEP INT NOT NULL DEFAULT 1,
    IF_CYCLE INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_T_DB_SEQ PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);

核心函數

• F_create_SEQ(@name)：建立序列
• F_GET_NEXT_SEQ_VAL(@name)：取得下一值（含並發鎖定保護）
• F_GET_SEQ_CUR_VAL(@name)：取得目前值
• F_SET_SEQ_VAL(@name, @val)：設定值
• F_DEL_SEQ(@name)：刪除序列
• F_SEQ_exists(@name)：檢查存在性

主從表關聯範例

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

索引策略：為何主鍵用 NONCLUSTERED？

• 代理鍵無業務查詢價值，不適合做叢集索引
• 叢集索引應留給高頻範圍查詢、排序、分組的業務欄位（如建立日期、客戶 ID）
• 每個資料表僅能有一個叢集索引，資源稀缺，善用之

分頁查詢實戰優勢

有了單一數值型主鍵，配合 ROW_NUMBER() OVER (ORDER BY U_ID) 或 OFFSET/FETCH，分頁查詢變得簡單高效，不再受困於多欄位排序與游標邏輯。