SQL获取最后插入的ID值SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY的比较

时间:2023-01-31 07:32:17
SQL Server 2000中,有三个比较类似的功能:他们分别是:SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY,它们都返回插入到 IDENTITY 列中的值。
nbsp;   ; IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
     @@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
     SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。
     例如,有两个表 T1 和 T2,在 T1 上定义了一个 INSERT 触发器。当将某行插入 T1 时,触发器被激发,并在 T2 中插入一行。此例说明了两个作用域:一个是在 T1 上的插入,另一个是作为触发器的结果在 T2 上的插入。
     假设 T1 和 T2 都有 IDENTITY 列,@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。
     @@IDENTITY 返回插入到当前会话中任何作用域内的最后一个 IDENTITY 列值,该值是插入 T2 中的值。
     SCOPE_IDENTITY() 返回插入 T1 中的 IDENTITY 值,该值是发生在相同作用域中的最后一个INSERT。如果在作用域中发生插入语句到标识列之前唤醒调用 SCOPE_IDENTITY() 函数,则该函数将返回 NULL 值。
而IDENT_CURRENT('T1') 和 IDENT_CURRENT('T2') 返回的值分别是这两个表最后自增的值。
====================================================================================
也许大家对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 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 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 是取全局操作的最后一步操作
所产生的自增域的值的。
=============================================================================

预知法

预知法,其实相对简单一些,我们可以设置一个主键,但该主键不设置为自增,因为在插入前,我们自己通过程序的方法获得一个唯一的值作为我们的主键.这样就避免了我们插入后不能获得主键的缺点,并且由于我们是预知我们要插入的值,所以在插入后,我们就可以不通过数据库提供的方法,再次获得主键.

在这里我推荐使用一种比较好的预知序列,这就是GUID.大家都知道任何两台计算机都不可能产生一样的GUID值,并且在一台机器上产生的GUID也不会重复.

我们在插入数据库前,自己通过GUID函数获得一个可用的GUID,然后用它做为主键来插入到数据库中.

但是这也有一个缺点:GUID一般都比较长16位,并且它不具有任何的含义,这样在大批量插入时有一定的性能影响.

后知法

后知法是本次我介绍的比较实用的方法,通过SQL Server的两个函数和一个系统变量获得当前最新的主键值

两个函数分别是:

IDENT_CURRENT

SCOPE_IDENTITY

系统变量值为:

@@IDENTITY

其中IDENT_CURRENT和SCOPE_IDENTITY的主要区别在于,SCOPE_IDENTITY主要用在一个会话中,只对当前会话插入的表的最后的IDENTITY,而IDENT_CURRENT没有作用域的概念,它用于特定的表.

其中我主要介绍一下系统变量@@IDENTITY,以下是SQL Server在线帮助提供的信息

@@IDENTITY
新增信息 - 2001 年 9 月

返回最后插入的标识值。

语法
@@IDENTITY

返回类型
numeric

注释
在一条 INSERT、SELECT INTO 或大容量复制语句完成后,@@IDENTITY 中包含此语句产生的最后的标识值。若此语句没有影响任何有标识列的表,则 @@IDENTITY 返回 NULL。若插入了多个行,则会产生多个标识值,@@IDENTITY 返回最后产生的标识值。如果此语句激发一个或多个执行产生标识值的插入操作的触发器,则语句执行后立即调用 @@IDENTITY 将返回由触发器产生的最后的标识值。如果触发器在具有标识列的表上执行插入操作后激发,并且触发器插入到另一个没有标识列的表中,则 @@IDENTITY 将返回第一个插入的标识值。若 INSERT 或 SELECT INTO 语句失败或大容量复制失败,或事务被回滚,则 @@IDENTITY 值不会还原为以前的设置。

在返回插入到表的 @@IDENTITY 列的最后一个值方面,@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 函数类似。

@@IDENTITY 和 SCOPE_IDENTITY 将返回在当前会话的所有表中生成的最后一个标识值。但是,SCOPE_IDENTITY 只在当前作用域内返回值,而 @@IDENTITY 不限于特定的作用域。

IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。IDENT_CURRENT 返回任何会话和任何作用域中为特定表生成的标识值。有关更多信息,请参见 IDENT_CURRENT。

@@IDENTITY 函数的作用域是执行该函数的本地服务器。此函数不能应用于远程或链接服务器。要获得其他服务器上的标识值,请在远程服务器或链接服务器上执行存储过程,并使该存储过程(在远程或链接服务器的环境中执行)收集标识值并将其返回本地服务器上的调用连接。

示例
下面的示例向带有标识列的表中插入一行,并用 @@IDENTITY 显示在新行中使用的标识值。

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

具体实现的方法是,写一个存储过程,并且在插入完成以后,返回@@IDENTITY,以下是一个存储过程的例子:

下列示例将创建两个表 TZ 和 TY,并在 TZ 上创建一个 INSERT 触发器。当将某行插入表 TZ 中时,触发器 (Ztrig) 将激发并在 TY 中插入一行。

USE tempdb
GO
CREATE TABLE TZ (
   Z_id int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks
Z_id   Z_name
-------------
1      Lisa
2      Mike
3      Carla

CREATE TABLE TY (
   Y_id int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
---------------
100   boathouse
105   rocks
110   elevator

/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and find out what identity values you get
with the @@IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT   @@IDENTITY AS [@@IDENTITY]
GO

--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/

@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier


值得注意的是SCOPE_IDENTITY返回的本过程中影响的表TZ,而触法器影响的TY表不被反映,而@@IDENTITY则反映当前所有影响的表,即包含触发器影响的TY表

在使用过程中我们可以使用RETURN @@identity返回一个存储过程获得的值,然后在程序中获得这个值,这样就能获得当前插入后生成的主键.