identify字段

在数据库中 , 常用的一个流水编号通常会使用 identity 字段来进行设置 , 这种编号的好处是一定不会重复 , 而且一定是唯一的 , 这对 table 中的唯一值特性很重要 , 通常用来做客户编号 , 订单编号等功能 , 以下介绍关于此种字段常用方式及相关技术 .

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

more..

less..
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, 所以系统就直接跳号处理啰 , 避免发生重复编号的问题 .

identity 字段是一项很重功的功能 , 若能善加利用 , 相信帮助很大 .
所有的数据皆可在 sql server help 内找到 , 也请多加利用 .