SQL Server 識別欄（Identity）完整指南：建立、種子/遞增、插入陷阱與並發優勢

資料表的主鍵若為流水號，手動維護極易衝突。SQL Server 識別欄（Identity Column）內建自動遞增機制，解決並發插入唯一性難題。

一、核心概念

• 一張表僅能有一個識別欄，通常設為主鍵
• 種子：起始值，預設 1
• 遞增：每筆遞增量，預設 1
• 語法：IDENTITY(1,1) → 種子 1、遞增 1
• 不可手動插入值，強制插入會報錯（除非開啟 SET IDENTITY_INSERT ON）

二、SSMS 圖形化設定步驟

1. 右鍵資料表 → 設計
2. 選中目標欄位（需為整數類型：tinyint, smallint, int, bigint, decimal(0) 等）
3. 下方「資料行屬性」→「識別規格」→「是識別」設為「是」
4. 同處可調整「識別種子」與「識別遞增」
5. 儲存（若跳出「阻止儲存...」請參考前文關閉選項）

三、T-SQL 建表範例

CREATE TABLE dbo.Orders (
    OrderID INT IDENTITY(1000, 1) PRIMARY KEY,  -- 從 1000 開始
    OrderDate DATETIME2 DEFAULT SYSUTCDATETIME(),
    CustomerID INT NOT NULL
);

四、常見操作與陷阱

五、並發優勢解析

多用戶同時 INSERT 時，SQL Server 透過內部鎖機制（Latch + 輕量級鎖）分配唯一識別值，無需應用層加鎖或序列表，效能極高。識別值分配後即使交易回滾，該值也不會重用（會產生間隙），這是設計使然，非錯誤。

六、進階替代方案（SQL Server 2012+）

SEQUENCE 物件：跨表共享號碼、可循環、可快取、不綁定特定欄位，適合分散式號碼生成場景。

CREATE SEQUENCE dbo.OrderSeq AS BIGINT START WITH 1 INCREMENT BY 1 CACHE 100;
-- 使用
INSERT INTO Orders (OrderID, ...) VALUES (NEXT VALUE FOR dbo.OrderSeq, ...);