您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 主鍵設計最佳實踐：代理鍵替代複合業務鍵，解決效能、分頁、維護三大痛點

編輯：關於SqlServer

常見反模式：業務複合主鍵層層傳遞

主表：PK (DocNo)
明細表：PK (DocNo, ItemNo)
子明細表：PK (DocNo, ItemNo, PriceType)
結果：每層都要攜帶所有上層鍵值，JOIN 條件冗長、索引寬大、分頁極難寫

痛點總結

問題	影響
資料冗餘儲存	上層鍵值在每層重複儲存，空間浪費
SQL 複雜度高	多欄位 JOIN、動態 SQL 拼接困難
程式參數爆炸	每層呼叫需傳遞多個鍵值參數
效能下降	寬索引、鎖升級風險、查詢計畫不穩定
Web 分頁困難	複合鍵無法直接用 `WHERE pk > @last_pk` 遊標分頁

解決方案：代理鍵

每張表新增 單一數值型代理鍵 作為主鍵，業務鍵改為 唯一索引/約束 保證業務唯一性。

-- 主表
CREATE TABLE DocHeader (
    DocID     BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,  -- 代理鍵
    DocNo     VARCHAR(20) NOT NULL,
    TotalAmt  MONEY,
    CONSTRAINT UQ_DocHeader_DocNo UNIQUE NONCLUSTERED (DocNo)  -- 業務唯一
)

-- 明細表
CREATE TABLE DocDetail (
    DetailID  BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    DocID     BIGINT NOT NULL,                           -- FK 指向代理鍵
    ItemNo    INT NOT NULL,
    Qty       INT,
    CONSTRAINT UQ_DocDetail_DocItem UNIQUE NONCLUSTERED (DocID, ItemNo),
    CONSTRAINT FK_DocDetail_Header FOREIGN KEY (DocID) REFERENCES DocHeader(DocID)
)

-- 子明細表
CREATE TABLE DocSubDetail (
    SubID     BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    DetailID  BIGINT NOT NULL,                           -- FK 指向上層代理鍵
    PriceType TINYINT NOT NULL,
    Amount    MONEY,
    CONSTRAINT UQ_DocSubDetail_DetailPrice UNIQUE NONCLUSTERED (DetailID, PriceType),
    CONSTRAINT FK_DocSubDetail_Detail FOREIGN KEY (DetailID) REFERENCES DocDetail(DetailID)
)

代理鍵產生策略：參考 Oracle Sequence

SQL Server IDENTITY 無法手動指定值、發行訂閱/還原麻煩。建議自建 序列表 模擬 Oracle Sequence：

CREATE TABLE DB_Sequence (
    SeqName   VARCHAR(50) NOT NULL,
    SeqOwner  VARCHAR(50) NOT NULL DEFAULT 'dbo',
    CurVal    BIGINT NOT NULL DEFAULT 0,
    MinVal    BIGINT NOT NULL DEFAULT 0,
    MaxVal    BIGINT NOT NULL DEFAULT 9223372036854775807,
    StepVal   INT NOT NULL DEFAULT 1,
    IsCycle   BIT NOT NULL DEFAULT 0,
    PRIMARY KEY CLUSTERED (SeqName, SeqOwner)
)

-- 取得下一值 (需在交易中序列化)
CREATE PROC GetNextSeq @SeqName VARCHAR(50), @NextVal BIGINT OUTPUT
AS
BEGIN
    BEGIN TRAN
    UPDATE DB_Sequence SET CurVal = CurVal + StepVal WHERE SeqName = @SeqName
    SELECT @NextVal = CurVal FROM DB_Sequence WHERE SeqName = @SeqName
    COMMIT TRAN
END

索引策略建議

代理鍵主鍵 → NONCLUSTERED (窄、不分頁、不更新)
叢集索引留給 高頻範圍查詢欄位 (如 DocDate, CreatedAt)
業務唯一鍵 → UNIQUE NONCLUSTERED
外鍵欄位 → NONCLUSTERED 索引支援 JOIN

Web 分頁範例 (代理鍵版)

-- 遊標式分頁 (效能最佳，無 OFFSET 掃描)
DECLARE @PageSize INT = 20, @LastDocID BIGINT = 1000
SELECT TOP (@PageSize) *
FROM DocHeader
WHERE DocID > @LastDocID
ORDER BY DocID

單一欄位比較，索引 Seek 极快，支援百萬級資料秒級翻頁。