SQL Server高效获取标识值技巧大揭秘！

创作时间:

作者:

@小白创作中心

SQL Server高效获取标识值技巧大揭秘！

引用

CSDN

等

来源

https://blog.csdn.net/vince6799/article/details/727925

https://www.c-sharpcorner.com/UploadFile/8911c4/different-between-scope-identity-ident-current-and-identit/

https://learn.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver16

https://www.cnblogs.com/liuqifeng/p/9149021.html

https://learn.microsoft.com/zh-cn/sql/relational-databases/security/sql-server-security-best-practices?view=sql-server-ver16

https://learn.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16#arguments

https://learn.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16#syntax

在SQL Server数据库开发中，获取新插入记录的标识值是一个常见需求。SQL Server提供了多种方法来实现这一功能，其中@@IDENTITY、SCOPE_IDENTITY()和IDENT_CURRENT('table_name')是最常用的三种。本文将详细介绍这三种方法的使用场景、区别以及最佳实践。

@@IDENTITY、SCOPE_IDENTITY()和IDENT_CURRENT('table_name')虽然都能获取标识值，但它们在作用域和会话范围上有所不同：

@@IDENTITY：返回当前会话中所有作用域内最后生成的标识值。这意味着如果在触发器中插入了新记录，@@IDENTITY将返回触发器中生成的标识值，而不是原始插入操作的标识值。
SCOPE_IDENTITY()：仅返回当前作用域内最后生成的标识值。作用域可以是存储过程、触发器、函数或批处理。因此，即使触发器中插入了新记录，SCOPE_IDENTITY()仍然会返回原始插入操作的标识值。
IDENT_CURRENT('table_name')：返回指定表在任何会话和任何作用域中最后生成的标识值。这个函数不受当前会话或作用域的限制，只关注特定表的标识值。

为了更好地理解这些函数的区别，我们可以通过一个示例来说明：

假设我们有两个表Table1和Table2，其中Table1上定义了一个INSERT触发器，当向Table1插入数据时，触发器会向Table2中插入一条默认值记录。

CREATE TABLE Table1(id int IDENTITY);
CREATE TABLE Table2(id int IDENTITY(100,1));

CREATE TRIGGER TG_Table1 ON Table1 FOR INSERT
AS
BEGIN
    INSERT INTO Table2 DEFAULT VALUES;
END;

当我们向Table1插入一条记录时，触发器会被激发并在Table2中插入一条记录。此时，@@IDENTITY、SCOPE_IDENTITY()和IDENT_CURRENT('table_name')将返回不同的值：

INSERT INTO Table1 DEFAULT VALUES;
SELECT @@IDENTITY; -- 返回Table2中插入的标识值
SELECT SCOPE_IDENTITY(); -- 返回Table1中插入的标识值
SELECT IDENT_CURRENT('Table1'); -- 返回Table1中插入的标识值
SELECT IDENT_CURRENT('Table2'); -- 返回Table2中插入的标识值

从上面的例子可以看出，@@IDENTITY和SCOPE_IDENTITY()的区别在于作用域的限制。而IDENT_CURRENT('table_name')则提供了更灵活的选择，允许我们指定要查询的表。

除了上述三种方法外，SQL Server还提供了另一种获取标识值的方式——OUTPUT子句。OUTPUT子句可以用于INSERT、UPDATE或DELETE语句中，返回受影响行的信息。与@@IDENTITY等函数相比，OUTPUT子句具有以下优势：

可以在单个语句中返回多行数据，而不仅仅是最后插入的一行。
不受触发器的影响，直接返回当前操作的结果。
可以与其他列一起返回，便于在应用程序中使用。

例如，我们可以使用OUTPUT子句来获取插入操作中生成的标识值：

DECLARE @InsertedIDs TABLE (ID int);

INSERT INTO Table1 (Column1, Column2)
OUTPUT INSERTED.ID INTO @InsertedIDs
VALUES ('Value1', 'Value2');

SELECT * FROM @InsertedIDs;

在这个例子中，我们创建了一个表变量@InsertedIDs来存储插入操作中生成的标识值。通过OUTPUT子句，我们可以将新生成的标识值直接插入到这个表变量中，然后在后续操作中使用这些值。

总结来说，在SQL Server中获取标识值有多种方法，每种方法都有其适用场景：