Access / SQL Server 自動編號 (Identity/AutoNumber) 種子、步長設定與重置完整語法

核心概念

• 種子：第一筆資料的值 (預設 1)。
• 步長：每遞增一次的增量 (預設 1)。
• **用途**：主鍵生成、合併多庫避免衝突 (奇/偶、不同起始值)、測試資料預留範圍。

SQL Server (T-SQL) 完整語法

1. 建表時指定

CREATE TABLE dbo.Orders (
    OrderID BIGINT IDENTITY(10000, 10) PRIMARY KEY, -- 從 10000 開始，每次 +10
    OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Amount DECIMAL(18,2)
);

2. 現有表修改種子/步長 (需重建資料行)

SQL Server **不支援** 直接 `ALTER COLUMN ... IDENTITY(new_seed, new_increment)`。
**標準做法**：新增資料行 → 移舊資料 → 刪舊欄 → 重命名 (或用 `SWITCH` 分區切換)。

3. 重置種子 (Reseed) — 常用於測試/清空表後

-- 檢查目前身分識別值
DBCC CHECKIDENT ('dbo.Orders', NORESEED);
-- Current identity value: 10050.

-- 重置為指定值 (下一筆插入將用此值 + 步長)
DBCC CHECKIDENT ('dbo.Orders', RESEED, 20000); -- 下一筆 = 20010

-- 重置為 0 (下一筆 = 1，假設步長 1)
DBCC CHECKIDENT ('dbo.Orders', RESEED, 0);

注意：表內**必須無資料**，或最大 ID 小於新種子，否則插入會主鍵衝突。

4. 允許手動插入 Identity 值 (SET IDENTITY_INSERT)

SET IDENTITY_INSERT dbo.Orders ON;
INSERT INTO dbo.Orders (OrderID, OrderDate, Amount) VALUES (9999, '2024-01-01', 100); -- 指定 ID
SET IDENTITY_INSERT dbo.Orders OFF;

Access (Jet/ACE SQL) 完整語法

1. 建表時指定 (COUNTER / AUTOINCREMENT)

-- 方式 1: COUNTER (種子, 步長)
CREATE TABLE Orders (
    OrderID LONG COUNTER(1000, 5) PRIMARY KEY,
    OrderDate DATETIME,
    Amount CURRENCY
);

-- 方式 2: AUTOINCREMENT (種子, 步長) - ANSI-92 模式
CREATE TABLE Orders (
    OrderID LONG AUTOINCREMENT(1000, 5) PRIMARY KEY,
    ...
);

2. 事後修改種子/步長 (ALTER TABLE ... ALTER COLUMN)

-- 修改下一個值為 2000，步長 50
ALTER TABLE Orders ALTER COLUMN OrderID COUNTER(2000, 50);

-- 重置從 1 開始，步長 1
ALTER TABLE Orders ALTER COLUMN OrderID COUNTER(1, 1);

**VBA 代碼中執行** ：

CurrentDb.Execute "ALTER TABLE Orders ALTER COLUMN OrderID COUNTER(1, 1)", dbFailOnError
' 或
DoCmd.RunSQL "ALTER TABLE Orders ALTER COLUMN OrderID COUNTER(1, 1)"

3. 關鍵警示：Access 不檢查唯一性！

執行 `ALTER COLUMN ... COUNTER` **不會驗證現有資料**，若表內已有 ID=5，重置為 1 會導致下一筆插入產生 **重複主鍵錯誤** (若設為 PK) 或 **靜默重複** (若無 PK)。
**安全流程**：

1. 備份資料庫。
2. 確認表內無資料，或手動更新現有 ID 避開新範圍。
3. 執行 ALTER。
4. 立即測試插入。

跨資料庫對比表

最佳實踐建議

1. 預設值 (1,1) 即可滿足 99% 場景，除非有分庫合併、測試隔離等特殊需求。
2. 合併多庫主鍵：採用 **複合主鍵** 或 **GUID/UNIQUEIDENTIFIER** 或 **Snowflake ID** (應用層生成)，避免 Identity 衝突。
3. 重置種子僅限開發/測試環境**，生產環境極少需求且風險高。
4. Access 專案**：務必將 AutoNumber 設為 **主鍵**，強制唯一性檢查，否則重置種子極易產生靜默資料破壞。