SQL Server IDENTITY 自增屬性深度解析：建立、修改、移除與重置種子完整實戰

IDENTITY 不只是「自動加 1」：種子、增量、種子重置、屬性移除全掌握

`IDENTITY(seed, increment)` 是 SQL Server 表級屬性，非資料類型。深入理解其參數、限制與變更操作，能避免遷移、重構、資料修復時的常見坑。

語法與參數

IDENTITY [ ( seed , increment ) ]

• seed (種子)：第一筆資料的值，預設 1。
• increment (增量)：每次遞增量，預設 1。兩者必須同時指定或同時省略。
• 範例：`IDENTITY(100, 5)` → 100, 105, 110...

建表範例

CREATE TABLE WorkRoom (
    WorkRoomID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    WorkRoomName VARCHAR(50) NULL,
    TeacherName VARCHAR(10) NULL,
    WorkRoomManager VARCHAR(10) NULL,
    WorkRoomPlan VARCHAR(MAX) NULL,
    Purl VARCHAR(50) NULL,
    Remarks VARCHAR(MAX) NULL
);

修改種子與增量 (僅限重置種子，增量不可改)

增量一旦建立無法修改。若需變更增量，必須重建欄位。

-- 重置種子 (下一筆插入值)
DBCC CHECKIDENT ('WorkRoom', RESEED, 0);  -- 下一筆為 1
DBCC CHECKIDENT ('WorkRoom', RESEED, 100); -- 下一筆為 101

移除 IDENTITY 屬性：核心難點與三大方案

鐵律：`ALTER TABLE ... ALTER COLUMN ...` 無法移除 IDENTITY。系統表 `sys.columns` 的 `is_identity` 位元不可直接清除 (舊版可透過系統表 Hack，極度不建議)。

方案 1：系統表 Hack (極高風險，僅供參考)

-- ⚠️ 生產環境嚴禁，可能導致元資料損壞
EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
UPDATE syscolumns SET colstat = colstat & 0x0000 
WHERE id = OBJECT_ID('表名') AND name = '欄位名';
EXEC sp_configure 'allow updates', 0; RECONFIGURE;

方案 2：重建欄位標準流程 (推薦，安全可控)

-- 以 zx_user.userid 為例
BEGIN TRANSACTION;

-- 1. 新增臨時欄位
ALTER TABLE zx_user ADD useridtmp INT NULL;

-- 2. 複製資料
UPDATE zx_user SET useridtmp = userid;

-- 3. 刪除 PK 約束 (必須先刪 PK 才能刪欄位)
-- 先查詢 PK 名稱
SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=OBJECT_ID('zx_user');
-- 假設 PK 名為 pk_zx_user
ALTER TABLE zx_user DROP CONSTRAINT pk_zx_user;

-- 4. 刪除原身分欄位
ALTER TABLE zx_user DROP COLUMN userid;

-- 5. 新增無 IDENTITY 欄位
ALTER TABLE zx_user ADD userid INT NULL;

-- 6. 還原資料
UPDATE zx_user SET userid = useridtmp;

-- 7. 刪除臨時欄位
ALTER TABLE zx_user DROP COLUMN useridtmp;

-- 8. 設定 NOT NULL
ALTER TABLE zx_user ALTER COLUMN userid INT NOT NULL;

-- 9. 重建 PK
ALTER TABLE zx_user ADD CONSTRAINT pk_zx_user PRIMARY KEY (userid);

COMMIT;

方案 3：SELECT INTO 新表 + 重命名 (大表高效)

-- 1. 複製結構與資料到新表 (不複製索引/約束/Identity)
SELECT * INTO zx_user_new FROM zx_user WHERE 1=0;  -- 僅結構
-- 或帶資料
SELECT * INTO zx_user_new FROM zx_user;

-- 2. 新表 userid 現為普通 INT，確認無 Identity
-- 3. 建立索引、PK、FK、預設值等
-- 4. 交換表名
EXEC sp_rename 'zx_user', 'zx_user_old';
EXEC sp_rename 'zx_user_new', 'zx_user';
-- 5. 驗證後刪除舊表

`SELECT INTO` 不複製 Identity、索引、約束、觸發器、權限，適合「去 Identity」重建場景。大表比逐步 ALTER 快得多。

查詢 Identity 資訊

-- 所有 Identity 欄位
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName, 
       seed_value, increment_value, last_value
FROM sys.identity_columns;

-- 特定表
SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID('WorkRoom');

總結建議

1. 設計階段審慎規劃 seed/increment，事後改增量極麻煩。
2. 移除 Identity 僅用方案 2 或 3，嚴禁系統表 Hack。
3. 大表優先 SELECT INTO 重建，小表用 ALTER 逐步重建。
4. 重置種子用 `DBCC CHECKIDENT`，配合 `RESEED` 參數精確控制。