SQL Server 身分識別欄位重置三大法：TRUNCATE、DBCC CHECKIDENT、IDENTITY_INSERT

三種重置身分識別值的方法比較

詳細用法與注意事項

1. TRUNCATE TABLE

TRUNCATE TABLE dbo.TestTable;
-- 等同於：DELETE + 重置身分識別 + 不記錄逐行日誌（極快）
-- 限制：有外鍵參考時無法執行、不觸發 DELETE 觸發器

2. DBCC CHECKIDENT (RESEED)

-- 重置為 0，下一筆插入將為 1（假設種子 1、增量 1）
DBCC CHECKIDENT ('dbo.TestTable', RESEED, 0);

-- 查看目前身分識別值狀態
DBCC CHECKIDENT ('dbo.TestTable');
-- 返回：目前身分識別值、資料行值、下一個值

-- 指定新種子（如從 1000 開始）
DBCC CHECKIDENT ('dbo.TestTable', RESEED, 1000);

關鍵行為：RESEED, 0 後，若表有資料且最大 ID > 0，下一筆插入會嘗試使用 1 → 衝突。建議搭配：RESEED, (SELECT MAX(ID) FROM Table) 或先清空資料。

3. SET IDENTITY_INSERT

-- 允許顯式插入身分識別欄位
SET IDENTITY_INSERT dbo.TestTable ON;

INSERT INTO dbo.TestTable (ID, Name) VALUES (999, '指定ID資料');

-- 插入完畢必須關閉
SET IDENTITY_INSERT dbo.TestTable OFF;

-- 限制：同一時間同一資料庫僅允許一張表開啟 IDENTITY_INSERT

實戰組合場景

場景 A：測試資料重置（保留結構、清空資料、重置種子）

TRUNCATE TABLE dbo.TestTable;  -- 最快、最乾淨

場景 B：生產資料修復種子跳號（保留資料、修正下一個值）

DECLARE @maxID INT = (SELECT MAX(ID) FROM dbo.TestTable);
DBCC CHECKIDENT ('dbo.TestTable', RESEED, @maxID);
-- 下一筆插入將為 @maxID + 1

場景 C：跨環境資料合併（指定主鍵值插入）

SET IDENTITY_INSERT dbo.TargetTable ON;

INSERT INTO dbo.TargetTable (ID, Col1, Col2)
SELECT ID, Col1, Col2 FROM dbo.SourceTable
WHERE ID NOT IN (SELECT ID FROM dbo.TargetTable);

SET IDENTITY_INSERT dbo.TargetTable OFF;

常見誤區與避坑

• ❌ DELETE FROM Table 不會重置身分識別種子 → 使用 TRUNCATE 或 DBCC CHECKIDENT
• ❌ DBCC CHECKIDENT RESEED, 0 後直接插入資料 → 若表有殘留資料會主鍵衝突
• ❌ 忘記 SET IDENTITY_INSERT OFF → 後續隱性插入失敗、鎖定該表
• ✅ 生產環境操作前先備份、在交易中執行、驗證 IDENT_CURRENT()