Identity的知识

在数据库中, 常用的一个流水编号通常会使用 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 字段.

注意：该值和后面3里的“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, 所以系统就直接跳号处理啰, 避免发生重复编号的问题.