資料表建立後需將既有欄位改為 IDENTITY，直接 ALTER COLUMN 不支援。本文提供兩種情境的標準解決方案：無資料表直接重建、有資料表建立臨時表遷移，並附完整可執行腳本與驗證步驟。
ALTER TABLE 表名 DROP COLUMN 欄位名;
ALTER TABLE 表名 ADD 欄位名 INT IDENTITY(1,1);/**************** 環境準備 ********************/
IF OBJECT_ID(N'test', N'U') IS NOT NULL DROP TABLE test;
CREATE TABLE test ( id INT NOT NULL, name VARCHAR(20) NOT NULL );
INSERT INTO test VALUES (1, '成龍'), (3, '章子怡'), (4, '劉若英'), (8, '王菲');
SELECT * FROM test;
/*********************************************/
BEGIN TRANSACTION;
-- 1. 建立目標結構的臨時表 (含 IDENTITY)
CREATE TABLE test_tmp (
id INT NOT NULL IDENTITY(1,1),
name VARCHAR(20) NOT NULL
);
GO
-- 2. 允許手動插入 IDENTITY 值
SET IDENTITY_INSERT test_tmp ON;
GO
-- 3. 複製資料 (保留原 id 值，使用鎖定提示防止並發衝突)
IF EXISTS (SELECT * FROM test)
EXEC('INSERT INTO test_tmp(id, name) SELECT id, name FROM test WITH (HOLDLOCK, TABLOCKX)');
GO
-- 4. 關閉手動插入
SET IDENTITY_INSERT test_tmp OFF;
GO
-- 5. 刪除原表
DROP TABLE test;
GO
-- 6. 重命名臨時表為原表名
EXEC sp_rename N'test_tmp', N'test', 'OBJECT';
GO
COMMIT;
GO
/**************** 驗證結果 ******************/
INSERT INTO test VALUES ('張曼'); -- 新資料自動取得 ID = 9 (接續原最大值 8)
SELECT * FROM test;
| 步驟 | 目的 | 關鍵語法 |
|---|---|---|
| 建立臨時表 | 定義目標結構 (含 IDENTITY) | CREATE TABLE ... IDENTITY(1,1) |
| IDENTITY_INSERT ON | 允許手動指定 ID 值複製舊資料 | SET IDENTITY_INSERT test_tmp ON |
| 複製資料 | 保留業務鍵值不變 | INSERT ... SELECT ... WITH (HOLDLOCK, TABLOCKX) |
| 重命名 | 無縫切換，應用端無感 | sp_rename 'test_tmp', 'test', 'OBJECT' |
| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| SSMS 設計師介面修改 | 視覺化、自動產生腳本 | 大表會鎖定久、產生臨時表佔空間 | 中小型表、開發環境 |
| 手動建臨時表遷移 (本文) | 可控、可腳本化、生產適用 | 步驟較多 | 生產環境、大表、需保留資料 |
| 匯出資料 → 重建表 → 匯入資料 | 工具支援好 (BCP/SSIS) | 需停機窗口、流程較長 | 超大表、跨版本遷移 |
SCH-M 鎖，安排維護時段執行。id 有間隙 (如範例 1,3,4,8)，新資料將從 MAX(id)+1 開始 (例中為 9)，不會回填間隙。id 為外鍵被參考，需先移除 FK → 修改 → 重建 FK。BEGIN TRAN ... COMMIT) 確保原子性，任一步驟失敗全盤回滾。掌握此標準遷移模式，能安全地將既有欄位升級為 IDENTITY，支援自動遞增需求且保留歷史資料完整性。
