使用預存程序模擬 IDENTITY(100,1) 自動遞增邏輯

情境

需在不使用 IDENTITY 欄位的情況下，實現從 100 起始、遞增 1 的序號產生機制（如訊息編號）。

實作：序號管理表 + 預存程序

1. 建立序號儲存表

CREATE TABLE common_info (
    name VARCHAR(10),
    value INT
)
INSERT INTO common_info VALUES ('msg_count', 100)

2. 取號預存程序（含並發保護）

CREATE PROC p_ins_alm_msg
    @msg_count INT OUTPUT
AS
BEGIN
    -- 取得目前值
    SELECT @msg_count = value 
    FROM common_info 
    WHERE name = 'msg_count'

    -- 嘗試原子性更新：舊值比對確保無競爭
    UPDATE common_info 
    SET value = @msg_count + 1 
    WHERE name = 'msg_count' AND value = @msg_count

    -- 若更新 0 列表被並發修改，回傳錯誤
    IF @@ROWCOUNT = 0
        RETURN -1000

    -- 使用新序號插入業務表
    INSERT INTO table1 (field0) VALUES (@msg_count)
    RETURN 0
END
GO

關鍵設計點

• 樂觀並發控制：WHERE value = @msg_count 確保更新基於讀取時的值，避免競爭條件
• 輸出參數回傳：呼叫端可直接取得配發序號
• 錯誤代碼：-1000 表並發衝突，呼叫端可重試

呼叫範例

DECLARE @next_id INT
EXEC @ret = p_ins_alm_msg @next_id OUTPUT
IF @ret = 0
    PRINT '配發序號：' + CAST(@next_id AS VARCHAR)
ELSE
    PRINT '並發衝突，請重試'

替代方案比較

若環境支援，優先使用 IDENTITY 或 SEQUENCE；僅在舊版本或特殊業務需求下採用序號表方案。

1. 上一篇文章： 三種縮減 SQL Server 交易記錄檔大小的實用方法
2. 下一篇文章： DBCC CHECKIDENT：IDENTITY 欄位種子值檢查與重設完整指南

關於SqlServer

SQL Server INSERT 錯誤「無法將 NULL 插入身分識別欄」解決方案

錯誤訊息：不能將值 NULL 插入列 id，表 web.db

通用分頁存儲過程隱藏 SQL 注入風險，參數化也無法完全防禦

前言：廣為流傳的分頁存儲過程其實暗藏殺機 在搜尋引擎輸入「分

SQL Server 叢集部署關鍵技巧：硬體、版本、單節點、節點擴充與自動化

前言：叢集是高可用性的基石，也是細節的藝術 Windows

SQL 資料操作語言 (DML) 核心：INSERT、UPDATE、DELETE 完全指南

SQL DML 三大核心語句深度解析 INSERT、UPDA