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

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

返回最后插入的标识值的系统函数。Is a system function that returns the last-inserted identity value.

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

语法Syntax

@@IDENTITY  

返回类型Return Types

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

RemarksRemarks

在一条 INSERT、SELECT INTO 或大容量复制语句完成后,@@IDENTITY 中包含语句生成的最后一个标识值。After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. 如果语句未影响任何包含标识列的表,则 @@IDENTITY 返回 NULL。If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. 如果插入了多个行,生成了多个标识值,则 @@IDENTITY 将返回最后生成的标识值。If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. 如果语句触发了一个或多个触发器,该触发器又执行了生成标识值的插入操作,那么,在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. 如果对包含标识列的表执行插入操作后触发了触发器,并且触发器对另一个没有标识列的表执行了插入操作,则 @@IDENTITY 将返回第一次插入的标识值。If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. 出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败,或者事务被回滚的情况时,@@IDENTITY 值不会恢复为以前的设置。The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

如果语句和事务失败,它们会更改表的当前标识,从而使标识列中的值出现不连贯现象。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.

@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数,因为他们都返回插入到表的 IDENTITY 列的最后一个值。@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

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

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

@@IDENTITY 函数的作用域是执行该函数的本地服务器上的当前会话。The scope of the @@IDENTITY function is current session on the local server on which it is executed. 此函数不能应用于远程或链接服务器。This function cannot be applied to remote or linked servers. 若要获得其他服务器上的标识值,请在远程服务器或链接服务器上执行存储过程,并使(在远程或链接服务器的环境中执行的)该存储过程收集标识值,并将其返回本地服务器上的发出调用的连接。To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

复制可能会影响 @@IDENTITY 值,因为该值在复制触发器及存储过程中使用。Replication may affect the @@IDENTITY value, since it is used within the replication triggers and stored procedures. 如果此列是复制项目的一部分,则 @@IDENTITY 不是最近用户创建的标识的可靠指示器。@@IDENTITY is not a reliable indicator of the most recent user-created identity if the column is part of a replication article. 你可以使用 SCOPE_IDENTITY() 函数语法代替 @@IDENTITY。You can use the SCOPE_IDENTITY() function syntax instead of @@IDENTITY. 有关详细信息,请参阅 SET ANSI_NULLS (Transact-SQL)For more information, see SCOPE_IDENTITY (Transact-SQL)

备注

必须重新编写调用存储过程或 Transact-SQLTransact-SQL 语句才能使用 SCOPE_IDENTITY() 函数,该函数会返回在用户语句作用域内所用的最新标识,而不是复制所用的嵌套触发器作用域内的标识。The calling stored procedure or Transact-SQLTransact-SQL statement must be rewritten to use the SCOPE_IDENTITY() function, which returns the latest identity used within the scope of that user statement, and not the identity within the scope of the nested trigger used by replication.

示例Examples

以下示例向包含标识列 (LocationID) 的表中插入一行,并使用 @@IDENTITY 显示新行中使用的标识值。The following example inserts a row into a table with an identity column (LocationID) and uses @@IDENTITY to display the identity value used in the new row.

USE AdventureWorks2012;  
GO  
--Display the value of LocationID in the last row in the table.  
SELECT MAX(LocationID) FROM Production.Location;  
GO  
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)  
VALUES ('Damaged Goods', 5, 2.5, GETDATE());  
GO  
SELECT @@IDENTITY AS 'Identity';  
GO  
--Display the value of LocationID of the newly inserted row.  
SELECT MAX(LocationID) FROM Production.Location;  
GO  

另请参阅See Also

System Functions (Transact-SQL) System Functions (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
IDENT_CURRENT (Transact-SQL) IDENT_CURRENT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
SCOPE_IDENTITY (Transact-SQL) SCOPE_IDENTITY (Transact-SQL)
SELECT (Transact-SQL)SELECT (Transact-SQL)