SQL Server IDENTITY 屬性深度解析：自動遞增欄位的控制與陷阱

語法與基本行為

IDENTITY (seed, increment)
-- seed: 起始值，預設 1
-- increment: 遞增步長，預設 1

標記為 IDENTITY 的欄位在 INSERT 時**不可指定值**，由引擎自動產生唯一遞增數值。常用於主鍵或代理鍵。

完整範例：建立、清空、手動插入、恢復自動遞增

-- 1. 建立資料表
CREATE TABLE Photos (
    PhotoID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    AlbumID INT NOT NULL,
    Caption NVARCHAR(50) NOT NULL
);

-- 2. 清空資料表（重設身分值為種子）
TRUNCATE TABLE Photos;

-- 3. 允許顯式插入身分值
SET IDENTITY_INSERT Photos ON;

INSERT INTO Photos (PhotoID, AlbumID, Caption) VALUES (1, 1, N'maql1');
INSERT INTO Photos (PhotoID, AlbumID, Caption) VALUES (1, 2, N'maql2'); -- 同 PhotoID 不同 AlbumID
INSERT INTO Photos (PhotoID, AlbumID, Caption) VALUES (2, 1, N'zhangr1');
-- ... 更多手動指定值 ...

-- 4. 關閉顯式插入，恢復自動遞增
SET IDENTITY_INSERT Photos OFF;

-- 後續 INSERT 僅指定非身分欄位
INSERT INTO Photos (AlbumID, Caption) VALUES (1, N'maql1');  -- PhotoID 自動變 3
INSERT INTO Photos (AlbumID, Caption) VALUES (2, N'maql2');  -- PhotoID 自動變 4

關鍵行為與限制

• TRUNCATE TABLE 會將身分值重設為 seed；DELETE 不會重設。
• SET IDENTITY_INSERT ON **一次只能對一張資料表生效**；嘗試對第二張表開啟會報錯。
• 手動插入的值**不影響**下一次自動產生的值；引擎內部維護「下一身分值」獨立於現有最大值。
• DBCC CHECKIDENT ('Photos', RESEED, 100) 可手動重設下一身分值（慎用，可能造成主鍵衝突）。
• SCOPE_IDENTITY() 取得當前會話、當前範圍內最後產生的身分值（優於 @@IDENTITY，不受觸發器影響）。

常見陷阱與最佳實務

1. 缺口問題：交易回滾、刪除記錄、手動插入大數值都會造成身分值不連續。**業務邏輯切勿依賴連續性**。
2. 複寫/合併場景：使用 IDENTITY(1,1) 易發生衝突；改用 IDENTITY(1,2) 搭配 IDENTITY(2,2)（奇/偶分離），或改用 UNIQUEIDENTIFIER / SEQUENCE（SQL 2012+）。
3. 大量匯入效能：BULK INSERT / bcp 時加上 KEEPIDENTITY 保留來源身分值，或 FIRE_TRIGGERS 控制觸發器行為。