慎用@@IDENTITY

最新推荐文章于 2025-01-08 10:26:10 发布

cngkqy

最新推荐文章于 2025-01-08 10:26:10 发布

阅读量1k

点赞数

分类专栏： SqlServer 文章标签： insert sql server table null go sql

SqlServer 专栏收录该内容

36 篇文章

订阅专栏

OK，也许大家对SQL Server中的 @@IDENTITY 都不陌生，都知道它是获取数据表中最后一条插入数据的IDENTITY值。比如，表 A 中有个 ID 为自增1的字段，假设此时 ID 的值为100，现在如果我往表A插入一条数据，并在插入后 SELECT @@IDENTITY，则其返回 101，最后一条IDENTITY域（即ID域）的值。

现在问题来了，为什么说要慎用@@IDENTITY呢？原因是 @@IDENTITY 它总是获取最后一条变更数据的自增字段的值，而忽略了进行变更操作所在的范围约束。比如，我有表 A 和表 B 两个表，现在我在表 A 上定义了一个Insert触发器，当在表 A 中插入一条数据时，自动在表 B 也插入一条数据。此时，大家注意，有两个原子操作：在A中插入一条数据，接着在B中随后插入一条数据。

现在我们想下，假设上面表 A 和表 B 都有IDENTITY自增域，那么我们在表 A 插入一条数据后，使用了 SELECT @@IDENTITY 输出时，输出的到底是 A 还是 B 的自增域的值呢？答案很明显，是谁最后插入就输出谁，那么就是 B 了。于是，我本意是想得到 A 的自增域值，结果得到了 B 的自增域值，一只 BUG 随之诞生，搞不好还会影响到整个系统数据的混乱。

因此，对于这种情况，建议大家慎用 @@IDENTITY，而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值，但是它是仅限在一个操作范围之内，而不像 @@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。

我们具体执行段微软提供的示范SQL语句看看就理解了：

1 USE tempdb
2 GO
3 CREATE TABLE TZ (
4 Z_id int IDENTITY ( 1 , 1 ) PRIMARY KEY ,
5 Z_name varchar ( 20 ) NOT NULL )
6
7 INSERT TZ
8 VALUES ( ' Lisa ' )
9 INSERT TZ
10 VALUES ( ' Mike ' )
11 INSERT TZ
12 VALUES ( ' Carla ' )
13
14 SELECT * FROM TZ
15
16 -- Result set: This is how table TZ looks.
17 Z_id Z_name
18 -- -----------
19 1 Lisa
20 2 Mike
21 3 Carla
22
23 CREATE TABLE TY (
24 Y_id int IDENTITY ( 100 , 5 ) PRIMARY KEY ,
25 Y_name varchar ( 20 ) NULL )
26
27 INSERT TY (Y_name)
28 VALUES ( ' boathouse ' )
29 INSERT TY (Y_name)
30 VALUES ( ' rocks ' )
31 INSERT TY (Y_name)
32 VALUES ( ' elevator ' )
33
34 SELECT * FROM TY
35 -- Result set: This is how TY looks:
36 Y_id Y_name
37 -- -------------
38 100 boathouse
39 105 rocks
40 110 elevator
41
42 /* Create the trigger that inserts a row in table TY
43 when a row is inserted in table TZ */
44 CREATE TRIGGER Ztrig
45 ON TZ
46 FOR INSERT AS
47 BEGIN
48 INSERT TY VALUES ( '' )
49 END
50
51 /* FIRE the trigger and determine what identity values you obtain
52 with the @@IDENTITY and SCOPE_IDENTITY functions. */
53 INSERT TZ VALUES ( ' Rosalie ' )
54
55 SELECT SCOPE_IDENTITY () AS [ SCOPE_IDENTITY ]
56 GO
57 SELECT @@IDENTITY AS [ @@IDENTITY ]
58 GO