萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 將現有欄位改為 IDENTITY 自動遞增的三種方案與避坑指南

前言

業務演進中,常需將現有資料表的某欄位 (如手動維護的流水號) 改為 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;

方案三:線上零停機 — 新增身分欄 + 同步雙寫 + 切換 (企業級)

  1. 新增欄位:ALTER TABLE dbo.TargetTable ADD NewId INT IDENTITY(1,1) NOT NULL; (元資料操作,瞬間完成,不掃描資料)。
  2. 同步雙寫:修改應用寫入邏輯,同時寫入舊欄位 (業務鍵) 與新欄位 (身分鍵);或建立 AFTER INSERT/UPDATE 觸發器自動同步 (舊→新)。
  3. 回填歷史資料:分批 UPDATE TOP (10000) ... SET NewId = OldId WHERE NewId IS NULL,避免長鎖。
  4. 驗證一致性:SELECT COUNT(*) FROM TargetTable WHERE NewId <> OldId 應為 0。
  5. 切換應用讀取路徑:修改查詢、外鍵、API 使用 NewId。
  6. 清理:移除舊欄位 OldId、觸發器、舊索引。

**優勢**:全程資料庫線上服務,無長時間鎖表;**風險**:雙寫期一致性保證複雜,需完整測試。

四、關鍵避坑檢查清單

風險點對策
外鍵參考舊欄位方案二/三中須同步重建 FK 指向新欄位,或暫時停用 FK 再啟用
複寫/CDC/Change Tracking變更追蹤可能失效,操作前停用,操作後重新啟用並驗證
應用程式硬編碼欄位名全域搜尋程式碼/設定檔/報表/ETL,同步修改
IDENTITY 種子/增量衝突方案二導入後,DBCC CHECKIDENT('TargetTable', RESEED, max_val) 重設種子
權限/視圖/同義詞腳本化重建,sp_rename 不自動更新相依物件定義

五、圖形化介面替代方案 (SSMS 設計師)

SSMS 表設計師 → 勾選欄位「身分規格」→ 「是」 → 存檔。**本質上同方案二**:建新表 → 導資料 → 刪舊表 → 改名。**大表慎用**,因 UI 逾時風險高,且無法控制交易隔離等級。

結語

改欄位為 IDENTITY 本質是**重建表**。空表用 DROP/ADD 秒級完成;有資料表用「臨時表 + IDENTITY_INSERT + 重命名」標準化停機操作;零停機需求採「新欄雙寫 → 回填 → 切換」藍綠部署模式。**無論哪種方案,完整備份、腳本化相依物件、驗證腳本、回滾預案,缺一不可。**

copyright © 萬盛學電腦網 all rights reserved