SQLSERVER2000中的标识值获取函数

IDENTITY列，也被很多人称为自增列，在SQL Server 2000中是由IDENTITY定义的。下面是一个与获取最后插入的记录的标识值相关的函数示例。

在SQL Server中，可以使用SCOPE_IDENTITY()、@@IDENTITY和IDENT_CURRENT()来获取最后插入的记录的值。二者的区别在于
SCOPE_IDENTITY()返回同一作用域中标识列中插入的最后一个标识值。范围是模块存储过程、触发器、函数或批处理。因此，如果两个语句在同一个存储过程、函数或批处理中，则它们在同一个范围内。
@@IDENTITY返回在当前会话的所有表中生成的最后一个标识值
IDENT_CURRENT()返回在任何范围内为任何会话和任何指定表生成的最后一个标识值
下面是一个示例来说明它们的区别。

- a)示例代码
-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-创建

创建表t1(id int IDENTITY，col int)
INSERT t1 SELECT 1
UNION ALL SELECT 2
创建表t2(id int IDENTITY，col int)
GO

为INSERT
AS
INSERT t1 SELECT 3
GO创建触发器TR_insert_t2

-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-测试三个功能..1
-SELECT[SCOPE _ IDENTITY()]= SCOPE _ IDENTITY()，
[@@IDENTITY]=@@IDENTITY，
[IDENT _ CURRENT()For t1]= IDENT _ CURRENT(N ' ' t1 ' ')，
[IDENT _ CURRENT()For T2]= IDENT _ CURRENT(N ' ' T2 ' ')

/*-T2的t1ident _ current()的Result
scope _ identity()@ @ identity ident _ current()
-

(受影响的行数为1)
-*/
Go

-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-测试三个功能..2
-SELECT[SCOPE _ IDENTITY()]= SCOPE _ IDENTITY()，
[@@IDENTITY]=@@IDENTITY，
[IDENT _ CURRENT()For t1]= IDENT _ CURRENT(N ' ' t1 ' ')，
[IDENT _ CURRENT()For T2]= IDENT _ CURRENT(N ' ' T2 ' ')

/*-T2的t1ident _ current()的Result
scope _ identity()@ @ identity ident _ current()
-

(受影响的行数为1)
-*/
Go

-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-测试三个功能..3
-执行以下代码* *
-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = select[scope _ identity()]=
[IDENT _ CURRENT()fort1]= IDENT _ CURRENT(N ' ' t1 ' ')，
[IDENT _ CURRENT()fort2]= IDENT _ CURRENT(N ' ' T2 ' ')

/*-T2的t1ident _ current()的Result
scope _ identity()@ @ identity ident _ current()
-

-= = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-删除测试环境
-= = = = = = = = =

- b)代码结果描述
从上面的代码可以看出:
IDENT_CURRENT()总是返回指定表最后插入的ID值
@@IDENTITY返回当前会话的ID值，无论是否在同一个作用域内。在测试1和2中可以看到，它返回触发器中插入的记录的ID值，而在由于当前会话没有插入的记录，它返回NULL
SCOPE_IDENTITY()返回当前会话的相同作用域的IDENTITY值，所以在测试1和2中，它的返回值不受触发器的影响，而在测试3中，由于当前会话没有插入的记录，它返回NULL。