業務演進中,常需將現有資料表的某欄位 (如手動維護的流水號) 改為 IDENTITY 自動遞增。但 ALTER TABLE ... ALTER COLUMN **不支援直接加 IDENTITY**。本文提供三種方案,覆蓋「無資料」「有資料」「生產零停機」三種場景,並強調備份與驗證鐵律。
適用:表無資料、或資料可接受清空重導。
ALTER TABLE dbo.TargetTable DROP COLUMN ColName;
ALTER TABLE dbo.TargetTable ADD ColName INT IDENTITY(1,1) NOT NULL;
-- 若為主鍵
ALTER TABLE dbo.TargetTable ADD CONSTRAINT PK_TargetTable PRIMARY KEY (ColName);
**核心技巧**:SET IDENTITY_INSERT ON 允許顯式插入身分值,保留歷史主鍵值;新資料自動遞增。
BEGIN TRANSACTION;
-- 1. 建立目標結構相同但含 IDENTITY 的暫存表
CREATE TABLE dbo.TargetTable_Tmp (
ColName INT NOT NULL IDENTITY(1,1), -- 新身分欄
Col2 VARCHAR(50) NOT NULL,
Col3 INT NULL,
CONSTRAINT PK_TargetTable_Tmp PRIMARY KEY (ColName)
);
-- 2. 開啟 IDENTITY_INSERT,導入現有資料 (保留原值)
SET IDENTITY_INSERT dbo.TargetTable_Tmp ON;
INSERT INTO dbo.TargetTable_Tmp (ColName, Col2, Col3)
SELECT ColName, Col2, Col3 FROM dbo.TargetTable WITH (HOLDLOCK, TABLOCKX); -- 序列化隔離,防並發寫入
SET IDENTITY_INSERT dbo.TargetTable_Tmp OFF;
-- 3. 原子切換:刪舊表 → 重命名新表
DROP TABLE dbo.TargetTable;
EXEC sp_rename N'dbo.TargetTable_Tmp', N'TargetTable', 'OBJECT';
-- 4. 重建外鍵、索引、權限、觸發器等相依物件 (需腳本化預先產生)
-- ...
COMMIT TRANSACTION;
-- 5. 驗證:插入新行不指定 ColName,應自動遞增
INSERT INTO dbo.TargetTable (Col2, Col3) VALUES ('New', 999);
SELECT * FROM dbo.TargetTable ORDER BY ColName DESC;
ALTER TABLE dbo.TargetTable ADD NewId INT IDENTITY(1,1) NOT NULL; (元資料操作,瞬間完成,不掃描資料)。AFTER INSERT/UPDATE 觸發器自動同步 (舊→新)。UPDATE TOP (10000) ... SET NewId = OldId WHERE NewId IS NULL,避免長鎖。SELECT COUNT(*) FROM TargetTable WHERE NewId <> OldId 應為 0。NewId。OldId、觸發器、舊索引。**優勢**:全程資料庫線上服務,無長時間鎖表;**風險**:雙寫期一致性保證複雜,需完整測試。
| 風險點 | 對策 |
|---|---|
| 外鍵參考舊欄位 | 方案二/三中須同步重建 FK 指向新欄位,或暫時停用 FK 再啟用 |
| 複寫/CDC/Change Tracking | 變更追蹤可能失效,操作前停用,操作後重新啟用並驗證 |
| 應用程式硬編碼欄位名 | 全域搜尋程式碼/設定檔/報表/ETL,同步修改 |
| IDENTITY 種子/增量衝突 | 方案二導入後,DBCC CHECKIDENT('TargetTable', RESEED, max_val) 重設種子 |
| 權限/視圖/同義詞 | 腳本化重建,sp_rename 不自動更新相依物件定義 |
SSMS 表設計師 → 勾選欄位「身分規格」→ 「是」 → 存檔。**本質上同方案二**:建新表 → 導資料 → 刪舊表 → 改名。**大表慎用**,因 UI 逾時風險高,且無法控制交易隔離等級。
改欄位為 IDENTITY 本質是**重建表**。空表用 DROP/ADD 秒級完成;有資料表用「臨時表 + IDENTITY_INSERT + 重命名」標準化停機操作;零停機需求採「新欄雙寫 → 回填 → 切換」藍綠部署模式。**無論哪種方案,完整備份、腳本化相依物件、驗證腳本、回滾預案,缺一不可。**