資料表已建立且含資料時,需將某欄位改為 IDENTITY(1,1) 自動增長,SQL Server **不支援直接 ALTER COLUMN ADD IDENTITY**。本文提供兩種標準方案:無資料表直接重建、有資料表臨時表遷移,並說明關鍵風險與驗證步驟。
最簡單、風險最低:
ALTER TABLE dbo.TargetTable DROP COLUMN ColName;
ALTER TABLE dbo.TargetTable ADD ColName INT IDENTITY(1,1) NOT NULL;
INSERT INTO dbo.TargetTable (OtherCols) VALUES (...); SELECT * FROM dbo.TargetTable;核心邏輯:建立新表(含 IDENTITY)→ 開啟 IDENTITY_INSERT 導入舊資料 → 刪除舊表 → 重命名新表。全程在交易中完成,確保原子性。
BEGIN TRANSACTION;
GO
-- 1. 建立目標結構相同但含 IDENTITY 的臨時表
CREATE TABLE dbo.TargetTable_tmp (
ID INT NOT NULL IDENTITY(1,1),
Name VARCHAR(20) NOT NULL
-- 其他欄位保持相同定義
);
GO
-- 2. 允許顯式插入 IDENTITY 欄位
SET IDENTITY_INSERT dbo.TargetTable_tmp ON;
GO
-- 3. 以 HOLDLOCK/TABLOCKX 確保搬移期間資料不被修改
IF EXISTS (SELECT 1 FROM dbo.TargetTable)
EXEC('INSERT INTO dbo.TargetTable_tmp (ID, Name)
SELECT ID, Name FROM dbo.TargetTable WITH (HOLDLOCK, TABLOCKX)');
GO
-- 4. 關閉 IDENTITY_INSERT
SET IDENTITY_INSERT dbo.TargetTable_tmp OFF;
GO
-- 5. 刪除舊表
DROP TABLE dbo.TargetTable;
GO
-- 6. 重命名新表為原名
EXEC sp_rename N'dbo.TargetTable_tmp', N'TargetTable', 'OBJECT';
GO
COMMIT TRANSACTION;
GO
-- 7. 驗證:插入新列應自動產生 ID
INSERT INTO dbo.TargetTable (Name) VALUES ('NewRow');
SELECT * FROM dbo.TargetTable ORDER BY ID;
| 風險 | 影響 | 對策 |
|---|---|---|
| 外鍵參考 | DROP TABLE 失敗 | 先 ALTER TABLE ... DROP CONSTRAINT 移除 FK,遷移後重建。 |
| Schema-bound 視圖/函數 | DROP TABLE 失敗 | 先移除 SCHEMABINDING 物件,遷移後重建。 |
| 觸發器/權限/擴充屬性 | 遺失 | 腳本化原物件定義,遷移後重新套用。 |
| 複寫/CDC/Change Tracking | 中斷追蹤 | 暫停複寫/CDC,遷移後重新啟用並同步。 |
| IDENTITY 種子值不連續 | 新資料從 MAX(ID)+1 開始,非 1 | 預期行為;如需重置種子:DBCC CHECKIDENT('TargetTable', RESEED, 0)(謹慎)。 |
| 並發存取 | 搬移期間寫入失敗/資料遺失 | 維護窗口執行;或使用 sp_rename 交換表名(需 Sch-M 鎖,同樣阻塞)。 |
**本質上 SSMS 也是執行上述「建臨時表→搬資料→換表」腳本**,但會自動處理相依物件腳本化,適合中小型表。
-- 1. 結構確認
SELECT COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID('TargetTable'), COLUMN_NAME, 'IsIdentity') AS IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TargetTable';
-- 2. 種子/增量值
DBCC CHECKIDENT('TargetTable', NORESEED); -- 顯示目前種子
-- 3. 新增測試
INSERT INTO TargetTable (Name) VALUES ('Test_Identity');
SELECT SCOPE_IDENTITY() AS NewID; -- 應回傳新產生的 ID
-- 4. 相依物件檢查
SELECT * FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('TargetTable');
SELECT * FROM sys.sql_expression_dependencies WHERE referenced_id = OBJECT_ID('TargetTable');
修改既有欄位為 IDENTITY **無捷徑**,核心皆為「重建表」。無資料直接 DROP/ADD,**有資料必須臨時表遷移並保護相依物件**。生產環境務必:完整備份 → 維護窗口 → 交易包裹 → 驗證腳本全跑通 → 方可提交。