SQL Server Identity 欄位操作技巧：IDENTITY_INSERT 與 DBCC CHECKIDENT

Identity (識別欄位) 自動遞增方便，但常遇兩大問題：

問題一：刪除資料導致編號不連續，需手動指定插入特定值

-- 允許顯式插入 Identity 值
SET IDENTITY_INSERT [Schema].[Table] ON;
GO
INSERT INTO [Schema].[Table] (Id, Col1, Col2) VALUES (99, 'A', 'B'); -- 指定 Id=99
GO
SET IDENTITY_INSERT [Schema].[Table] OFF;  -- 必須關閉，否則後續 INSERT 必須指定值
GO

• 同一 Session 同一時間僅能有一張表開啟 IDENTITY_INSERT ON。
• 需擁有資料表 ALTER 權限。

問題二：TRUNCATE/大量刪除後 Identity 種子值過大，需重置

-- 檢查目前種子值 (不重置)
DBCC CHECKIDENT ('Schema.Table', NORESEED);
-- 重置種子為 1 (下一筆插入將為 2)
DBCC CHECKIDENT ('Schema.Table', RESEED, 1);
-- 重置為指定值 (如 100)
DBCC CHECKIDENT ('Schema.Table', RESEED, 100);

• RESEED：重置目前 Identity 值。
• NORESEED：僅回報目前值。
• 若表有資料，RESEED, 1 會將種子設為 1，但下一筆插入值 = MAX(Identity) + 1 (不會重複現有值)。
• TRUNCATE TABLE 會自動重置 Identity 種子至定義初始值。

補充：取得最後插入 Identity 值

• SCOPE_IDENTITY()：當前範圍 (預存程序/觸發器/批次) 最後產生的 Identity 值，**最推薦**。
• @@IDENTITY：當前 Session 最後產生的 Identity 值 (含觸發器產生的)，可能不準確。
• IDENT_CURRENT('Table')：指定資料表最後產生的 Identity 值 (跨 Session)，不受範圍限制。