@@IDENTITY、IDENTITY、IDENT_CURRENT与SCOPE_IDENTITY

 

@@IDENTITY

返回最后插入的标识值。

语法

@@IDENTITY

返回类型

numeric

注释

在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含此语句产生的最后的标识值。若此语句没有影响任何有标识列的表，则 @@IDENTITY 返回 NULL。若插入了多个行，则会产生多个标识值，@@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 显示在新行中使用的标识值。

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

IDENTITY（函数）

只用在带有 INTO table 子句的 SELECT 语句中，以将标识列插入到新表中。

尽管类似，但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。

语法

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

参数

data_type

标识列的数据类型。标识列的有效数据类型可以是任何整数数据类型分类的数据类型（bit 数据类型除外），也可以是 decimal 数据类型。

seed

要指派给表中第一行的值。给每一个后续行指派下一个标识值，该值等于上一个 IDENTITY 值加上 increment 值。如果既没有指定 seed，也没有指定 increment，那么它们都默认为 1。

increment

用来添加到 seed 值以获得表中连续行的增量。

column_name

将插入到新表中的列的名称。

返回类型

返回与 data_type 相同的类型。

注释

因为该函数在表中创建一个列，所以必须用下列方式中的一种在选择列表中指定该列的名称：

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable

示例

下面的示例将来自 pubs 数据库中 employee 表的所有行都插入到名为 employees 的新表。使用 IDENTITY 函数在 employees 表中从 100 而不是 1 开始编标识号。

USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'employees')
   DROP TABLE employees
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num, 
   fname AS first, 
   minit AS middle, 
   lname AS last, 
   IDENTITY(smallint, 100, 1) AS job_num, 
   job_lvl AS job_level, 
   pub_id, 
   hire_date
INTO employees 
FROM employee
GO
USE pubs
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

IDENT_CURRENT
返回为任何会话和任何作用域中的指定表最后生成的标识值。
语法
IDENT_CURRENT('table_name')
参数
table_name
是将要返回其标识值的表的名称。table_name 的数据类型为 varchar，没有默认值。
返回类型
sql_variant
注释
IDENT_CURRENT 类似于 Microsoft® SQL Server™ 2000 标识函数 SCOPE_IDENTITY 和 @@IDENTITY。这三个函数都返回最后生成的标识值。但是，它们在定义"最后"的作用域和会话上不同。
IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值。
示例
下面的示例说明由 IDENT_CURRENT、@@IDENTITY 和 SCOPE_IDENTITY 返回的不同的标识值。
USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT 
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT   * FROM t6
--id is empty.

SELECT   * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY      
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()   
/* Returns the value 1, which was inserted by the 
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action 
so far in this session.*/

SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action 
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/

SCOPE_IDENTITY
返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。
语法
SCOPE_IDENTITY( )
返回类型
sql_variant
注释
SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 在功能上相似，因为它们都返回插入到 IDENTITY 列中的值。
IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关更多信息，请参见 IDENT_CURRENT。
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 值。
有关说明，请参见示例。
示例
下列示例将创建两个表 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