by Kimi
9. November 2009 16:09
Kimi
在写SQL的时候，我们经常会遇到一种情况。那就是一个表中的某个列是自增的标识列，在插入记录到这个表后，我们需要返回这个标识列的值。
我们通常都会习惯性的使用@@IDENTITY 来获得这个值。其实SQL Server 不只提供一个@@IDENTITY这个一个方式来获得新插入自增标识列的值，还有 SCOPE_IDENTITY 和 IDENT_CURRENT 这两个函数。下面我们就来看看这个3个函数的区别，以及我们如何正确的选用它们。
下面先给出MSDN上对这3个函数的描述：
|
@@IDENTITY
返回最后插入的标识值的系统函数
备注：
在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含语句生成的最后一个标识值。如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL。如果插入了多个行，生成了多个标识值，则 @@IDENTITY 将返回最后生成的标识值。如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则 @@IDENTITY 将返回第一次插入的标识值。出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 值不会恢复为以前的设置。
|
|
SCOPE_IDENTITY()
返回插入到同一作用域中的标识列内的最后一个标识值。一个范围是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。
备注：
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。
|
|
IDENT_CURRENT
返回为某个会话和作用域中指定的表或视图生成的最新的标识值。
备注：
IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。在空表中调用 IDENT_CURRENT 函数时，此函数将返回 NULL。
|
从MSDN上看，这3个函数的主要区别就是一个会话和作用域的问题：
-
IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。
-
@@IDENTITY 返回为跨所有作用域的当前会话中的某个表生成的最新标识值。
-
SCOPE_IDENTITY 返回为当前会话和当前作用域中的某个表生成的最新标识值。
接下来，我们需要通过示例来看看这个3个函数的区别
|
-- 创建一个表TA, 为它定义一个自增列ID, 默认这个列是从开始自增.
CREATE TABLE TA(ID int IDENTITY);
-- 创建另一个表TB, 为它定义一个自增列ID, 设置这个列是从开始自增.
CREATE TABLE TB(ID int IDENTITY(100,1));
GO
-- 为表TA 创建一个INSERT的出发器,让给TA 表插入数据后, 给TB 表也插入数据.
CREATE TRIGGER TA_INSERT ON TA FOR INSERT
AS
BEGIN
INSERT TB DEFAULT VALUES
END;
GO
SELECT * FROM TA;
--ID is empty.
SELECT * FROM TB;
--ID is empty.
-- 在一个会话(session)中运行这些语句. --------------------
-- 向TA表插入一条默认记录.
INSERT TA DEFAULT VALUES;
SELECT @@IDENTITY;
/* 返回的值是100. 因为使用的是@@IDENTITY, 它得到的结果是触发器执行的插入数据到TB表后返回的标识列的值.*/
SELECT SCOPE_IDENTITY();
/* 返回的值是1. 因为向TA表插入的一条记录, 然而SCOPE_IDENTITY()是不能垮作用域的,那么它只获得插入TA表操作时返回的标识列的值.*/
SELECT IDENT_CURRENT('TB');
/* 返回插入TB表的标识列的值, 100.*/
SELECT IDENT_CURRENT('TA');
/* 返回插入TA表的标识列的值, 1.*/
-- 在另一个会话(session)中运行这些语句.
SELECT @@IDENTITY;
/* 返回的是NULL, 因为当前会话(session)中没有插入操作.*/
SELECT SCOPE_IDENTITY();
/* 返回的是NULL, 因为当前会话(session)当前作用域中没有插入操作*/
SELECT IDENT_CURRENT('TB');
/* 返回最后插入TB表的标识列的值, 100.*/
|
注意的一点是，作用域指的是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。我们来看一个示例：
|
-- 创建一个存储过程, 其功能就是往TA表插入一条记录
CREATE PROCEDURE usp_ins_TA
AS
BEGIN
INSERT TA DEFAULT VALUES;
END
-- 插入一条记录到TA表.
INSERT TA DEFAULT VALUES;
-- 调用存储过程插入一条记录到TA表.
EXECUTE usp_ins_TA;
SELECT @@IDENTITY;
/* 返回的结果是2, 因为执行了两次插入操作. */
SELECT SCOPE_IDENTITY();
/* 返回的结果是1, 因为尽管执行了两次操作, 但是第二次操作是调用一个存储过程, 那么它属于另一个范围内. */
SELECT IDENT_CURRENT('TA');
/* 返回的结果是2, 因为执行了两次插入操作. */
|
通过这两个示例，我想大家应该明白了这个3个函数的用法。（Kimi 2009-03-25）
Subscribe