SQL Server 的自動編號欄位運用

在資料庫中, 常用的一個流水編號通常會使用 identity 欄位來進行設置, 這種編號的好處是一定不會重覆, 而且一定是唯一的, 這對table中的唯一值特性很重要, 通常用來做客戶編號, 訂單編號等功能, 以下介紹關於此種欄位常用方式及相關技術.

後面的範例表皆以此表為建立:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

1. 在程式中取得identity值:

因為 identity 特性, 所以在 insert into 該 table 時, 不能指定該 identity 欄位值, 僅能指定其他欄位值, 而 identity 由資料庫維護, 所以一般要在 insert 後取得該 identity 欄位值, 則通常使用下面方式:
insert into products (product) values ('saw')
select @@identity
利用全域變數 @@identity 來取得最後影響的 insert 後產生的 identity 值, 如此一來便能方便地使用 identity 欄位.

2. 若要啟用識別插入(identity insert)時, 也就是如空缺號要指定 identity 欄位值時, 或者是處理資料表整理或備出時, 會用到的方式:

set identity_insert products on
insert into products (id, product) value (3, 'screwdriver')
要注意的地方是可以 insert 空缺號, 也可以加至最後, 但系統會自動更新 identity 至最大值, 要注意一旦啟用 identity_insert 時, 就一定要給定 identity 值, 另外並不能 update 該 identity 欄位值, 也就是說 identity_insert 該 identity 欄位僅 for insert, 不能 update.

3. 查詢目前 identity 值:

有時我們需要查詢目前 table 中該 identity 欄位最大值是多少時, 可以利用 dbcc 指令, 如下:
dbcc checkident('product', NORESEED)
可以獲得目前最大值的結果.

4. 重設目前最大 identity 值:

一樣利用 dbcc 指令, 如下:
dbcc checkident('product', RESEED, 100)
如此一來, 便能將目前的最大 identity 值指向100, 當然若故意設比目前最大值小時, 系統仍會接受, 但若 identity 遇上重覆資料時(如將 identity 設為 primary key時), 將會發生重大問題, 該 table 變成無法 insert 資料, 因為會發生 primary key violation, 解決方法當然就是將目前的 identity 修復, 直接使用
dbcc checkident('product', RESEED) 或
dbcc checkident('product')
(兩者等義)即可順利修復.

5. identity 欄位遇上 rollback 時:
當 identity 欄位碰到 rollback 時, 會發生跳號現象, 也就是說在 transaction 中, insert 了一筆資料, 但又 rollback 時, 該 identity 號會消失, 如下測試:
begin tran
insert into products (product) values ('test rollback')
rollback tran
dbcc checkident('product', NORESEED)
這個觀念很重要, 因為要維持 identity 特性, 但又發生 rollback, 所以系統就直接跳號處理囉, 避免發生重覆編號的問題.