IDENTITY属性使用

转自：http://www.cnblogs.com/seusoftware/p/3804333.html

 

一. 获取IDENTITY列值
插入了数据，有时还需要获取刚才生成的序列值另作他用，返回给前端也好，或者插入其他将来需要关联的表。

记得曾经有个面试题：假设当前表IDENTITY列最大值为N，在存储过程中，对这个表插入1行数据，获取到的IDENTITY列值有时小于或者大于N+1，可能是什么原因？

获取IDENTITY列值有三种方式：
(1) IDENT_CURRENT( 'table_name' ) 返回为任何会话和任何作用域中的特定表最后生成的标识值。
(2) @@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
(3) SCOPE_IDENTITY() 返回为当前会话和当前作用域中的任何表最后生成的标识值。

IDENT_CURRENT( 'table_name' ) 针对特定表，是全局的。@@IDENTITY和SCOPE_IDENTITY()针对所有表，区别在于作用域，也就是上下文：
(1) 如果当前INSERT语句上有函数，触发器等(不同作用域的)对象返回的IDENTITY值，那么@@IDENTITY会取所有表上的最后1个，而不是当前表上的；

(2) SCOPE_IDENTITY()会取当前作用域所有表上最后1个IDENTITY值，被调用的函数，触发器已经超出了作用域/上下文。所以在使用INSERT后，接着使用SCOPE_IDENTITY()获取IDENTITY列值，就不会有问题了：

insert test values('z');
select SCOPE_IDENTITY() as curr_value

一个GO语句/批处理，也是一个上下文的分界点，但是SQL语句是顺序执行的，所以一个会话里，只要在INSERT之后用SCOPE_IDENTITY()来获取IDENTITY值是没问题的。

 

 

二. 在IDENTITY列上做增删改操作(DML)
(1) 删除操作没有问题，直接DELETE即可

delete test where id = 2

(2) 如果要显式INSERT某个值，需要开启IDENTITY_INSERT这个SESSION级的选项

set IDENTITY_INSERT test on;
insert test(id,c1) values(3,'c');
set IDENTITY_INSERT test off;
select * from test

(3) 如果要UPDATE IDENTITY列值，无论是否开启IDENTITY_INSERT这个选项都无法更新

set IDENTITY_INSERT test on;
update test set id = 10 where id = 1
set IDENTITY_INSERT test off;
/*
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'id'.
*/

非要修改的话，就得借助中间表，在不含IDENTITY属性的中间表里做完UPDATE，然后再把数据导回来。中间表可参考上面的脚本。