SQL Server 2005 身分識別欄位 (自動編號) 完整設定指南

身分識別欄位 讓資料庫自動產生連續唯一值 (如流水號、主鍵)，免除應用程式手動管理 ID 的負擔。本文涵蓋建立、取值、重置種子、SSMS 圖形化操作與常見陷阱。

核心限制

• 每張表僅能有一個身分識別欄位
• 資料型別限定：tinyint, smallint, int, bigint, decimal(p,0), numeric(p,0)
• 必須設為 NOT NULL
• 不可為主鍵欄位單獨設定身分識別 (但通常主鍵即身分識別)

一、建立資料表時指定

CREATE TABLE dbo.UserInfor (
  UserID INT IDENTITY(100, 2) NOT NULL,  -- 起始 100，遞增 2
  UserName NCHAR(10) NOT NULL
);

IDENTITY(seed, increment)：seed 為第一筆資料值，increment 為後續每筆遞增量。

二、SSMS 圖形化介面設定

1. 物件總管 → 右鍵資料表 → 設計
2. 選取目標欄位 → 取消勾選 允許 Null
3. 下方 欄位屬性 → 展開 身分識別規格
4. 是身分識別 選 是
5. 設定 身分識別種子 (預設 1) 與 身分識別遞增 (預設 1)
6. 儲存資料表 (Ctrl+S)

三、插入資料後取得新產生的 ID

INSERT INTO dbo.UserInfor (UserName) VALUES ('張三');
SELECT SCOPE_IDENTITY() AS NewID;  -- 最佳實踐

四、重置種子值 (TRUNCATE TABLE 後或手動調整)

-- 將種子重置為 0，下一筆插入將為 0 + increment
DBCC CHECKIDENT ('UserInfor', RESEED, 0);

• TRUNCATE TABLE 會自動重置種子為建立時的初始值
• DELETE 不會 重置種子，續號遞增
• 頻繁 DELETE 導致 ID 不連續屬正常現象，若業務要求連續請改用序號表或應用層管理

五、完整範例腳本

CREATE TABLE mytest (
  id INT PRIMARY KEY IDENTITY(1,1),
  name VARCHAR(20) UNIQUE NOT NULL,
  Age TINYINT,
  notetime SMALLDATETIME DEFAULT GETDATE()
);
INSERT INTO mytest (name, age) VALUES ('張三', 20);
INSERT INTO mytest (name, age) VALUES ('李四', 20);
SELECT SCOPE_IDENTITY(); -- 取得最後新增的 id

常見問題 Q&A

Q: 身分識別值用完怎麼辦？

A: INT 上限 21 億，BIGINT 上限 922 兆，實務極難用盡。若真用盡需變更資料型別或重新設計。

Q: 可以指定身分識別欄位插入自訂值嗎？

A: 可以，使用 SET IDENTITY_INSERT table_name ON，插入完成後記得 OFF。

Q: 複寫/合併複寫環境下身分識別怎麼辦？

A: 需設定 自動身分識別範圍管理 或手動分配不重疊範圍 (如伺服器 A 用 1~100萬，B 用 100萬~200萬)。