SCOPE_IDENTITY (Transact-SQL)SCOPE_IDENTITY (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回插入到同一作用域中的标识列内的最后一个标识值。Returns the last identity value inserted into an identity column in the same scope. 一个范围是一个模块:存储过程、触发器、函数或批处理。A scope is a module: a stored procedure, trigger, function, or batch. 因此,如果两个语句处于同一个存储过程、函数或批处理中,则它们位于相同的作用域中。Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SCOPE_IDENTITY()  

返回类型Return Types

numeric(38,0)numeric(38,0)

RemarksRemarks

SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是相似的函数,因为它们都返回插入到标识列中的值。SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。IDENT_CURRENT returns the value generated for a specific table in any session and any scope. 有关详细信息,请参阅 IDENT_CURRENT (Transact-SQL)For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. 但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

例如,有两个表 T1 和 T2,并且在 T1 上定义了 INSERT 触发器。For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. 当将某行插入 T1 时,触发器被激发,并在 T2 中插入一行。When a row is inserted to T1, the trigger fires and inserts a row in T2. 该方案演示了两个作用域:在 T1 上的插入,以及在 T2 通过触发器的插入。This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

假设 T1 和 T2 都有标识列,@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY 返回在当前会话中的任何作用域内插入的最后一个标识列的值。@@IDENTITY returns the last identity column value inserted across any scope in the current session. 这是在 T2 中插入的值。This is the value inserted in T2. SCOPE_IDENTITY() 返回在 T1 中插入的 IDENTITY 值。SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. 这是在同一个作用域内发生的最后的插入。This was the last insert that occurred in the same scope. 如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数,则该函数返回 NULL。The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

如果语句和事务失败,它们会更改表的当前标识,从而使标识列中的值出现不连贯现象。Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. 即使未提交试图向表中插入值的事务,也永远无法回滚标识值。The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. 例如,如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败,表的当前标识值仍然会增加。For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

示例Examples

A.A. 将 @@IDENTITY 和 SCOPE_IDENTITY 用于触发器Using @@IDENTITY and SCOPE_IDENTITY with triggers

下面的示例创建两个表,TZTY,并对 TZ 创建一个 INSERT 触发器。The following example creates two tables, TZ and TY, and an INSERT trigger on TZ. 当将某行插入表 TZ 中时,触发器 (Ztrig) 将激发并在 TY 中插入一行。When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a row in 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'),('Mike'),('Carla');  
  
SELECT * FROM TZ;  

结果集:表 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'), ('rocks'), ('elevator');  
  
SELECT * FROM TY;  

结果集:TY 如下所示:Result set: This is how TY looks:

Y_id  Y_name  
---------------  
100   boathouse  
105   rocks  
110   elevator  

创建一个触发器,在表 TZ 中插入行时,该触发器会在表 TY 中插入行。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;  

触发该触发器,并确定使用 @@IDENTITY 和 SCOPE_IDENTITY 函数得到的值。FIRE the trigger and determine what identity values you obtain 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 returns the last identity value in the same scope. This was the insert on table TZ.*/`  
SCOPE_IDENTITY  
4  

/*@@IDENTITY returns the last identity value inserted to TY by the trigger. 
  This fired because of an earlier insert on TZ.*/
@@IDENTITY  
115  

B.B. 将 @@IDENTITY 和 SCOPE_IDENTITY() 用于复制Using @@IDENTITY and SCOPE_IDENTITY() with replication

下面的示例说明如何针对为合并复制发布的数据库中的插入内容使用 @@IDENTITYSCOPE_IDENTITY()The following examples show how to use @@IDENTITY and SCOPE_IDENTITY() for inserts in a database that is published for merge replication. 示例中的两个表都在 AdventureWorks2012AdventureWorks2012 示例数据库中,其中 Person.ContactType 未发布,Sales.Customer 已发布。Both tables in the examples are in the AdventureWorks2012AdventureWorks2012 sample database: Person.ContactType is not published, and Sales.Customer is published. 合并复制将把触发器添加到已发布的表中。Merge replication adds triggers to tables that are published. 因此,@@IDENTITY 可以从复制系统表中的插入内容而非用户表中的插入内容返回值。Therefore, @@IDENTITY can return the value from the insert into a replication system table instead of the insert into a user table.

Person.ContactType 表的最大标识值为 20。The Person.ContactType table has a maximum identity value of 20. 如果在该表中插入一行,@@IDENTITYSCOPE_IDENTITY() 将返回相同的值。If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return the same value.

USE AdventureWorks2012;  
GO  
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');  
GO  
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  
GO  
SELECT @@IDENTITY AS [@@IDENTITY];  
GO  

下面是结果集:Here is the result set.

SCOPE_IDENTITY  
21  
@@IDENTITY  
21

Sales.Customer 表的最大标识值为 29483。The Sales.Customer table has a maximum identity value of 29483. 如果在该表中插入一行,@@IDENTITYSCOPE_IDENTITY() 将返回不同的值。If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() 从用户表的插入内容返回值,而 @@IDENTITY 从复制系统表中的插入内容返回值。SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table. 请对需要访问插入的标识值的应用程序使用 SCOPE_IDENTITY()Use SCOPE_IDENTITY() for applications that require access to the inserted identity value.

INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);  
GO  
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  
GO  
SELECT @@IDENTITY AS [@@IDENTITY];  
GO  

下面是结果集:Here is the result set.

SCOPE_IDENTITY  
29484  
@@IDENTITY  
89

另请参阅See Also

@@IDENTITY (Transact-SQL)@@IDENTITY (Transact-SQL)