IDENTITY标识列的一些事

IDENTITY标识列的一些事

最后编辑：2010-12-26

IDENTITY（seed , increment）可以定义一个标识列。Seed表示种子值（缺省为1），increment表示增量（缺省为1）。一张表只能定义一个标识列。

一. IDENTITY列不能保证连续性
IDENTITY不能保证标识号是连续的，这是因为如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

二. IDENTITY列不能保证唯一性
缺省情况下，IDENTITY列是不能够手工插入的，但是假如SET IDENTITY_INSERT tab ON（缺省情况下为OFF），那么久可以手工插入了，基于此原因，所以IDENTITY列也不能保证该列值唯一，所以你要确保该列值唯一，必须使用约束PRIMARY KEY或者UNIQUE。

三. IDENTITY列在插入后才生成
IDENTITY列只有数据真正插入后才生成。基于此原因，若表存在INSTEAD OF触发器，那么在触发器中的INSTEADED表中的IDENTITY列的值永远为0。
同时我们若想提前获取IDENTITY也是不可能的，虽然你可以通过DENTITY_CURRENT +INCREMENT获得（下面即将讨论到），但是在多用户的网络环境下这是不可靠的。

四. 使用IDENTITY（）函数插入标识列

只有 在SELECT...INTO 子句中可以使用函数IDENTITY新生成一个标识列：

IDENTITY ( data_type [ , seed , increment ] ) AS column_name
小提示 ：
在SELECT...INTO中IDENTITY（）函数生成的序号是不受ORDER BY或TOP子句控制的。若想生成由ORDER BY/TOP指定的顺序，可以先创建一个包含IDENTITY属性列的新表，然后使用INSERT…SELECT（带ORDER BY或TOP子句）导入数据就行了。
参考：http://support.microsoft.com/?scid=kb%3Ben-us%3B273586&x=5&y=7

五. IDENT_CURRENT

IDENT_CURRENT 返回指定表生成的最新标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。如果对空表或没有标识列的表调用 IDENT_CURRENT 函数，则该函数返回 NULL。请谨慎使用IDENT_CURRENT 来预报下一个生成的标识值。由于有其他会话执行的插入，因此实际生成的值可能不同于 IDENT_CURRENT 加上 IDENTITY_SEED。
 

六．@@IDENTITY和SCOPE_IDENTITY
SCOPE_IDENTITY 和 @@IDENTITY 是在一条 INSERT、SELECT INTO 或大容量复制语句（bulk copy）完成后，返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域（所谓作用域(scope)是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中）。和上面的IDENT_CURRENT做个比较总结：

1. 如果插入语句未影响任何包含标识列的表，则 @@IDENTITY和SCOPE_IDENTITY都 返回 NULL。
2. 如果插入了多个行，生成了多个标识值，则 @@IDENTITY和SCOPE_IDENTITY将返回最后生成的标识值。

3. 如果是非插入语句，@@IDENTITY和SCOPE_IDENTITY值不变。
4. 如果语句插入表T1触发了一个或多个触发器（是AFTER触发器，4同。），该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。而SCOPE_IDENTITY（）却只返回T1表的插入后标识值，即使T1表是嵌套触发，也只返回直接插入T1后的标识值。
举例如下：
create table ta(id int identity(1,1))
go
create trigger tr
on ta
after insert
as
begin
  if (select count(*) from ta) < 5 -–不超过5个则嵌套触发。
    insert into ta default values
end
go
insert into ta default values
select @@identity, scope_identity()  --返回5和1

5. 如果对包含标识列的表T1执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表T2执行了插入操作，则 @@IDENTITY 和SCOPE_IDENTITY都将将返回第一次插入T1的标识值。
6. 出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 和SCOPE_IDENTITY（）值不会恢复为以前的设置。
7. 如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

七．INSTEAD OF INSERT触发器的情况
当表上建有INSTEAD OF INSERT触发器，对表的所有插入动作(称为外部插入)，都会被INSTEAD OF INSERT触发器截获，而真正对表的插入动作(称为内部插入)是发生在INSTEAD OF INSERT触发器中的。
因为SCOPE_IDENTITY()受作用域影响，因此在外部插入语句后的SCOPE_IDENTITY()是不起作用的，都返回NULL；而 内部插入语句后的SCOPE_IDENTITY()却是起作用的，返回表中标志列的最后值。
@@IDENTITY却一如既往地发挥作用，返回所有触发器生成的最后一个标志值。

举例如下：
create table a(id int not null identity(1, 1), code char(10))

go
create trigger tra
on a
instead of insert
as
begin
  select SCOPE_IDENTITY()  --返回NULL
  insert into a
  select code from inserted
  select SCOPE_IDENTITY()  --返回id列最后标志值
end
go
insert into  a
select 'a'
select SCOPE_IDENTITY()  --返回NULL
题外话 ：在外部INSERT中使用OUTPUT字句也不会返回标志值的，而内部INSERT中使用OUTPUT字句却是可以的。