SQL Server 自動編號身分識別欄完整指南：建立、種子重置、ALTER TABLE 限制與跨平台對比

前言

身分識別欄位是 SQL Server 實現自動遞增主鍵的核心機制。本文系統整理圖形化建立、DDL 語法、種子重置、ALTER TABLE 修改限制、Access 對比，並提供跨平台自增語法對照表。

方法一：SSMS 圖形化介面建立

1. 右鍵資料表 →「設計」。
2. 選取 id 欄位（int 型別）。
3. 屬性視窗：識別規格 → 是。
4. 設定 識別種子（起始值，預設 1）與 識別遞增（步長，預設 1）。

方法二：DDL 建表時指定 IDENTITY

CREATE TABLE MyTable (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  -- 種子=1, 遞增=1
    Col1 VARCHAR(50),
    Col2 DATETIME
);

語法：IDENTITY(seed, increment)

方法三：Access 專用 COUNTER/ AUTOINCREMENT（不適用 SQL Server）

-- Access 專用
CREATE TABLE MyTable (
    ID COUNTER(1000, 10) PRIMARY KEY,  -- 起始 1000, 步長 10
    Col1 VARCHAR(50)
);

-- 修改 Access 自增種子/步長
ALTER TABLE MyTable ALTER COLUMN ID COUNTER(2000, 50);

-- 重置空表 Access 自增從 1 開始
ALTER TABLE MyTable ALTER COLUMN ID COUNTER(1, 1);

警告：SQL Server 不支援 COUNTER、AUTOINCREMENT、ALTER COLUMN ... COUNTER 語法。

SQL Server 核心限制：無法 ALTER 現有欄位為 IDENTITY

許多開發者嘗試：

-- 錯誤嘗試
ALTER TABLE MyTable ALTER COLUMN ID IDENTITY(1,1);  -- 語法錯誤

原因：IDENTITY 屬性只能在 中定義（即 CREATE TABLE 或 ALTER TABLE ADD 時），不能在 ALTER COLUMN 中新增或修改。

正確替代方案

1. 新增身分識別欄位：

   ALTER TABLE MyTable ADD NewID INT IDENTITY(1,1);

2. 若必須保留原欄位名：新增新欄 → 複製數據 → 刪除舊欄 → 重命名新欄（需處理主鍵、外鍵、預設值等相依物件）。

種子值重置：DBCC CHECKIDENT

-- 下一筆插入從 101 開始
DBCC CHECKIDENT ('MyTable', RESEED, 100);

-- 重置為 1（表必須為空或 TRUNCATE 後）
TRUNCATE TABLE MyTable;
DBCC CHECKIDENT ('MyTable', RESEED, 0);  -- 下一筆為 1

跨平台自增語法對照表

避坑指南

• IDENTITY 不保證連續：交易回滾、插入失敗會造成間隙，業務若需連續號碼請用 Sequence + 應用層控制。
• 複寫環境：加上 NOT FOR REPLICATION 避免訂閱端產生衝突值。
• 大數據量插入：考慮 IDENTITY_INSERT ON 批次指定值，或使用 Sequence (SQL 2012+) 取號後批次寫入。
• 資料類型選擇：預估資料量選 BIGINT (922京) 而非 INT (21億)，避免溢位重構。

總結

SQL Server 的 IDENTITY 功能強大但限制明確：只能在建表或 ADD COLUMN 時定義，無法 ALTER COLUMN 追加。掌握 DBCC CHECKIDENT 種子重置、跨平台語法差異、複寫與大數據場景的最佳實踐，即可靈活駕馭自增主鍵設計。