代码改变世界

SQL Server中 @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT的比较

2013-06-07 12:03  AceYue  阅读(1313)  评论(0)  编辑  收藏  举报

Introduction：

     我们在很多应用程序的解决方案中都需要返回插入到SQL Query 最后一行的信息，这时我们有三种选择：
•@@IDENTITY
•SCOPE_IDENTITY
•IDENT_CURRENT
    这三个参数都是返回最后一行自增列（IDENTITY）数据，但是我们在不同的情况下使用这三个函数都有什么区别呢？
 

Compare：

 @@IDENTITY

 
他返回的是最后一行的自动生成的Identity值，适合所有的当前会话中，适合所有范围。
我们来看看Microsoft官方的描述吧：

      在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含语句生成的最后一个标识值。 

        如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL。 如果插入了多个行，生成了多个标识值，则 @@IDENTITY 将返回最后生成的标识值。 如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。 如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则 @@IDENTITY 将返回第一次插入的标识值。 出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 值不会恢复为以前的设置。

      如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。 

      即使未提交试图向表中插入值的事务，也永远无法回滚标识值。 例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

      @@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数，因为他们都返回插入到表的 IDENTITY 列的最后一个值。

      @@IDENTITY 和 SCOPE_IDENTITY 可以返回当前会话中的所有表中生成的最后一个标识值。 

但是，SCOPE_IDENTITY 只在当前作用域内返回值，而 @@IDENTITY 不限于特定的作用域。

       IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。 

       IDENT_CURRENT 可以返回任何会话和任何作用域中为特定表生成的标识值。 有关详细信息，请参阅IDENT_CURRENT (Transact-SQL)。

       @@IDENTITY 函数的作用域是执行该函数的本地服务器上的当前会话。 

       此函数不能应用于远程或链接服务器。 若要获得其他服务器上的标识值，请在远程服务器或链接服务器上执行存储过程，并使（在远程或链接服务器的环境中执行的）该存储过程收集标识值，并将其返回本地服务器上的发出调用的连接。

       复制可能会影响 @@IDENTITY 值，因为该值在复制触发器及存储过程中使用。 

       如果此列是复制项目的一部分，则 @@IDENTITY 不是最近用户创建的标识的可靠指示器。您可以使用 SCOPE_IDENTITY() 函数语法代替 @@IDENTITY。 有关详细信息，请参阅 SCOPE_IDENTITY (Transact-SQL)。 
 

注意:

       必须重新编写调用存储过程或 Transact-SQL 语句才能使用 SCOPE_IDENTITY() 函数，该函数将返回在用户语句作用域内所用的最新标识，而不是复制所用的嵌套触发器作用域内的标识。
 

     

SCOPE_IDENTITY 

 
这个函数返回当前会话中最后一行自动生成列的值，只适合当前会话范围。
Microsoft官方的描述：

     SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是相似的函数，因为它们都返回插入到标识列中的值。

     IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。 IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。 有关详细信息，请参阅IDENT_CURRENT (Transact-SQL)。

     SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。 但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

     例如，有两个表 T1 和 T2，并且在 T1 上定义了 INSERT 触发器。 当将某行插入 T1 时，触发器激发，并在 T2 中插入一行。 该方案演示了两个作用域：在 T1 上的插入，以及在 T2 通过触发器的插入。

      假设 T1 和 T2 都有标识列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句结束后返回不同的值。        @@IDENTITY 将返回在当前会话中的任何作用域内插入的最后一个标识列的值。 这是在 T2 中插入的值。 SCOPE_IDENTITY() 将返回在 T1 中插入的 IDENTITY 值。 这是在同一个作用域内发生的最后的插入。 如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数，则该函数将返回 Null。

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

IDENT_CURRENT

 
返回为指定的表或视图生成的最后一个标识值。 所生成的最后一个标识值可以针对任何会话和任何作用域。
Microsoft官方的描述：

      IDENT_CURRENT 类似于 SQL Server 2000 标识函数 SCOPE_IDENTITY 和 @@IDENTITY。 这三个函数都返回最后生成的标识值。 但是，上述每个函数中定义的“最后”的作用域和会话有所不同。

    • IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。
    • @@IDENTITY 返回为跨所有作用域的当前会话中的某个表生成的最新标识值。
    • SCOPE_IDENTITY 返回为当前会话和当前作用域中的某个表生成的最新标识值。

      如果 IDENT_CURRENT 值为 NULL（因为表从未包含行或已被截断），IDENT_CURRENT 函数将返回种子值。

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

      请谨慎使用 IDENT_CURRENT 来预报下一个生成的标识值。 由于其他会话执行的插入，实际生成的值可能与 IDENT_CURRENT 加上 IDENT_INCR 不同。

 

SQL Query:

以下的实例希望对你有所帮助：
CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty. 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
--/* Returns the value 1. This was inserted by the
--INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('Child');

--/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

--/* Returns value inserted into Parent. 
--This was the INSERT statement four statements before this query.*/ 

-- Do the following in Session 2.

SELECT @@IDENTITY;

--/* Returns NULL because there has been no INSERT action
--up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

--/* Returns NULL because there has been no INSERT action
--up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

--/* Returns the last value inserted into Child.*/