用 SQL 語句實現自訂遞增序號：以輔助表模擬 IDENTITY(100,1)

當無法使用 IDENTITY 或 SEQUENCE (SQL 2012+)，或需跨表/跨資料庫共用序號、自訂起始值與遞增步長時，可用輔助序號表 + 預存程序 + 悲觀鎖 實現執行緒安全的自訂遞增邏輯。

輔助表結構

CREATE TABLE Common_Sequences (
    SeqName   SYSNAME PRIMARY KEY,   -- 序號名稱，如 'Msg_ID', 'Order_No'
    CurrVal   BIGINT NOT NULL,       -- 目前值
    StepVal   INT NOT NULL DEFAULT 1,-- 遞增步長
    MinVal    BIGINT NOT NULL,       -- 最小值
    MaxVal    BIGINT NOT NULL        -- 最大值
);

-- 初始化：從 100 開始，遞增 1
INSERT INTO Common_Sequences VALUES (N'Msg_Count', 100, 1, 1, 9223372036854775807);

取號預存程序 (悲觀鎖 + 交易)

CREATE PROCEDURE dbo.sp_GetNextSeq
    @SeqName   SYSNAME,
    @NextVal   BIGINT OUTPUT,
    @RetryCnt  INT = 5               -- 重試次數，應對高併發
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Attempt INT = 0;

    WHILE @Attempt < @RetryCnt
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            -- 取得排他鎖 (UPDLOCK) + 保持到交易結束 (HOLDLOCK)
            SELECT @NextVal = CurrVal
            FROM Common_Sequences WITH (UPDLOCK, HOLDLOCK)
            WHERE SeqName = @SeqName;

            IF @NextVal IS NULL
            BEGIN
                RAISERROR(N'序號 %s 不存在', 16, 1, @SeqName);
                ROLLBACK TRANSACTION; RETURN -1;
            END

            -- 更新為下一值
            UPDATE Common_Sequences
            SET CurrVal = CurrVal + StepVal
            WHERE SeqName = @SeqName;

            COMMIT TRANSACTION;
            RETURN 0;  -- 成功
        END TRY
        BEGIN CATCH
            IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
            SET @Attempt += 1;
            IF @Attempt >= @RetryCnt
            BEGIN
                DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
                RAISERROR(N'取號失敗: %s', 16, 1, @ErrMsg);
                RETURN -2;
            END
            WAITFOR DELAY '00:00:00.01';  -- 10ms 退讓
        END CATCH
    END
END
GO

使用範例

DECLARE @MsgID BIGINT;
EXEC dbo.sp_GetNextSeq N'Msg_Count', @MsgID OUT;
-- @MsgID = 100 (第一次), 101 (第二)...

-- 寫入業務表
INSERT INTO Messages (MsgID, Content, CreateTime)
VALUES (@MsgID, N'測試訊息', SYSDATETIME());

進階：高併發無鎖替代方案 — SEQUENCE (SQL 2012+)

CREATE SEQUENCE dbo.Seq_Msg_Count
    AS BIGINT
    START WITH 100
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    NO CYCLE
    CACHE 1000;  -- 記憶體快取，極高效能

-- 取號
SELECT NEXT VALUE FOR dbo.Seq_Msg_Count;  -- 單值
-- 或批次
DECLARE @RangeStart BIGINT = NEXT VALUE FOR dbo.Seq_Msg_Count;  -- 取得第一個
-- 後續 999 個可直接 +1 使用 (需自行管理)

• CACHE 可大幅降低磁碟 I/O，但重啟會遺漏快取內的號碼 (不連續但不重複)。
• NO CACHE 保證連續但效能較低。

方案選擇指南

注意事項

• 輔助表方案必須加 UPDLOCK, HOLDLOCK，否則高併發下會發生臟讀/重複號碼。
• 交易時間極短 (單一 UPDATE)，鎖持有極短，併發表現尚可。
• 若序號用於對外單號 (訂單號、發票號)，建議加上前綴/年月/校驗碼，避免洩露業務量。

自訂遞增序號是常見需求，選對工具 (SEQUENCE 優先) 並正確處理並發，即能滿足業務與效能雙重要求。