SQL Server IDENTITY 欄位全生命週期管理指南

IDENTITY(seed, increment) 是 SQL Server 實現自動編號的核心機制。本文涵蓋建表指定、圖形介面操作、種子/步長修改、重置策略、ALTER COLUMN 限制與跨平台對比。

建表時指定

CREATE TABLE dbo.Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,  -- 種子 1，步長 1
    CustId INT NOT NULL,
    Amt DECIMAL(12,2)
);

圖形介面 (SSMS) 設定

1. 右鍵表 → 設計。
2. 選中 OrderId 欄位 (資料類型 int)。
3. 屬性視窗 → 標識規格 → 是。
4. 標識種子 (起始值，如 1) / 標識遞增 (步長，如 1)。

修改種子與步長：DBCC CHECKIDENT

-- 重置種子為 100 (下一筆插入將為 101)
DBCC CHECKIDENT ('dbo.Orders', RESEED, 100);

-- 僅檢查當前種子值
DBCC CHECKIDENT ('dbo.Orders');

注意：若表有資料且新種子 ≤ 現有最大值，下次插入會主鍵衝突。

清空表並重置種子為 1

-- 方法 1：TRUNCATE (最快、重置種子、不觸發觸發器、需無 FK 參考)
TRUNCATE TABLE dbo.Orders;

-- 方法 2：DELETE + DBCC (有觸發器、有 FK 時)
DELETE FROM dbo.Orders;
DBCC CHECKIDENT ('dbo.Orders', RESEED, 0);  -- 下一筆為 1

重大限制：無法用 ALTER COLUMN 將現有欄位改為 IDENTITY

-- 錯誤寫法
ALTER TABLE dbo.Orders ALTER COLUMN OrderId INT IDENTITY(1,1);  -- 語法錯誤

原因：IDENTITY 屬性僅存在於 <column_definition> (建表/加欄位時)，不屬於 ALTER COLUMN 可修改的屬性集。

變通方案：新增 IDENTITY 欄位替換舊欄位

-- 1. 新增臨時 IDENTITY 欄位
ALTER TABLE dbo.Orders ADD NewId INT IDENTITY(1,1);

-- 2. 更新業務邏輯使用 NewId (或建視圖/同步觸發器過渡)

-- 3. 刪除舊欄位 (需處理 FK/索引/預設值)
ALTER TABLE dbo.Orders DROP COLUMN OrderId;

-- 4. 重命名 (可選)
EXEC sp_rename 'dbo.Orders.NewId', 'OrderId', 'COLUMN';

Access 專用：COUNTER 型別 (SQL Server 不支援)

-- Access 可將現有欄位改為自動編號
ALTER TABLE tbl ALTER COLUMN Col COUNTER(2000, 50);  -- 種子 2000，步長 50
-- SQL Server 無 COUNTER 型別，不可用

跨資料庫自動編號對照表

資料庫	語法/機制	重置種子	修改現有欄位
SQL Server	IDENTITY(seed,inc)	DBCC CHECKIDENT	不可直接修改，需加欄位替換
MySQL	AUTO_INCREMENT	ALTER TABLE t AUTO_INCREMENT=1	ALTER TABLE t MODIFY col INT AUTO_INCREMENT (8.0+)
PostgreSQL	GENERATED AS IDENTITY / SERIAL	ALTER SEQUENCE seq RESTART WITH 1	ALTER TABLE t ALTER col ADD GENERATED AS IDENTITY (10+)
Oracle 12c+	GENERATED AS IDENTITY	ALTER TABLE t MODIFY col GENERATED AS IDENTITY (START WITH 1)	支援直接修改
Oracle ≤11g	Sequence + Trigger	Drop/Create Sequence	需重建 Trigger

最佳實務

• 主鍵用 BIGINT IDENTITY(1,1) (8 位元組，足夠 922 京筆)，避免 INT 溢位。
• 分散式系統考慮 SEQUENCE (可跨表/跨庫共用) 或 UUID / Snowflake ID。
• 種子/步長業務無意義，勿賦予業務含義 (如年份編碼)，改用計算欄位/視圖生成顯示碼。