Scope_Identity()，Identity()，@@ Identity和Ident_Current()有什么区别? [英] What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

问题描述

我知道Scope_Identity()，Identity()，@@Identity和Ident_Current()都可以获取标识列的值，但是我想知道它们之间的区别.

我遇到的争议的一部分是它们在上面作用于这些功能的范围是什么意思?

我还喜欢一个使用它们的不同场景的简单示例吗?

解决方案

• @@identity函数返回在同一会话中创建的最后一个身份.
• scope_identity()函数返回在同一会话和相同范围中创建的最后一个身份.
• ident_current(name)返回在任何会话中为特定表或视图创建的最后一个身份.
• identity()函数不用于获取身份，它用于在select...into查询中创建身份.

会话是数据库连接.范围是当前查询或当前存储过程.

scope_identity()和@@identity函数不同的情况是，如果您在表上有一个触发器.如果您有插入记录的查询，导致触发器在某处插入另一条记录，则scope_identity()函数将返回查询创建的标识，而@@identity函数将返回触发器创建的标识.

因此，通常您会使用scope_identity()函数.

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current() all get the value of the identity column, but I would love to know the difference.

Part of the controversy I'm having is what do they mean by scope as applied to these functions above?

I would also love a simple example of different scenarios of using them?

解决方案

• The @@identity function returns the last identity created in the same session.
• The scope_identity() function returns the last identity created in the same session and the same scope.
• The ident_current(name) returns the last identity created for a specific table or view in any session.
• The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

So, normally you would use the scope_identity() function.

这篇关于Scope_Identity()，Identity()，@@ Identity和Ident_Current()有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！