@@IDENTITY, SCOPE_IDENTITY(), OUTPUT 等检索最后一个身份的方法

2021-12-10 00:00:00 sql identity tsql sql-server output-clause

我见过在插入后检索主键标识字段的值时使用的各种方法.

I have seen various methods used when retrieving the value of a primary key identity field after insert.

declare @t table (
    id int identity primary key,
    somecol datetime default getdate()
)
insert into @t
default values

select SCOPE_IDENTITY() --returns 1
select @@IDENTITY --returns 1

插入后返回身份表:

Create Table #Testing (  
    id int identity,  
    somedate datetime default getdate()  
)  
insert into #Testing  
output inserted.*  
default values

什么方法是正确的或更好的?OUTPUT 方法范围安全吗?

What method is proper or better? Is the OUTPUT method scope-safe?

第二个代码片段是从野外 SQL

推荐答案

这取决于你想做什么...

It depends on what you are trying to do...

@@IDENTITY

返回在连接上生成的最后一个 IDENTITY 值，无论生成该值的表如何，也无论生成该值的语句的范围如何.@@IDENTITY 将返回在当前会话中输入到表中的最后一个标识值.@@IDENTITY 仅限于当前会话，不限于当前范围.例如，如果您在一个表上有一个触发器，导致在另一个表中创建一个身份，您将获得最后创建的身份，即使它是创建它的触发器.

Returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. @@IDENTITY is limited to the current session and is not limited to the current scope. For example, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SCOPE_IDENTITY()

SCOPE_IDENTITY()

返回在连接上和由同一范围内的语句生成的最后一个 IDENTITY 值，而不管生成该值的表如何.SCOPE_IDENTITY() 类似于@@IDENTITY，但它也会将值限制为您当前的范围.换句话说，它将返回您明确创建的最后一个标识值，而不是由触发器或用户定义的函数创建的任何标识.

Returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY() is similar to @@IDENTITY, but it will also limit the value to your current scope. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

IDENT_CURRENT()

IDENT_CURRENT()

返回在表中生成的最后一个 IDENTITY 值，而不管生成该值的语句的连接和范围如何.IDENT_CURRENT 仅限于指定的表，但不受连接或范围的限制.

Returns the last IDENTITY value produced in a table, regardless of the connection and scope of the statement that produced the value. IDENT_CURRENT is limited to a specified table, but not by connection or scope.

相关文章