SQL @@IDENTITY, SCOPE_IDENTITY(), OUTPUT 和其他检索最后一个身份的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/481395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
提问by Seibar
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
Returning a table of identities following insert:
插入后返回身份表：
Create Table #Testing (
id int identity,
somedate datetime default getdate()
)
insert into #Testing
output inserted.*
default values
What method is proper or better? Is the OUTPUT method scope-safe?
什么方法是正确的或更好的？OUTPUT 方法范围安全吗？
The second code snippet was borrowed from SQL in the Wild
第二个代码片段是从SQL in the Wild借来的
回答by mson
It depends on what you are trying to do...
这取决于你想要做什么......
@@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.
返回在连接上生成的最后一个 IDENTITY 值，无论生成该值的表如何，也无论生成该值的语句的范围如何。@@IDENTITY 将返回在当前会话中输入到表中的最后一个标识值。@@IDENTITY 仅限于当前会话，不限于当前范围。例如，如果您在一个表上有一个触发器，导致在另一个表中创建一个身份，您将获得最后创建的身份，即使它是创建它的触发器。
SCOPE_IDENTITY()
SCOPE_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.
返回在连接上和由同一范围内的语句生成的最后一个 IDENTITY 值，而不管生成该值的表如何。SCOPE_IDENTITY() 类似于@@IDENTITY，但它也会将值限制为您当前的范围。换句话说，它将返回您明确创建的最后一个标识值，而不是由触发器或用户定义的函数创建的任何标识。
IDENT_CURRENT()
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.
返回在表中生成的最后一个 IDENTITY 值，而不管生成该值的语句的连接和范围如何。IDENT_CURRENT 仅限于指定的表，但不受连接或范围的限制。
回答by Simon D
Note that there is a bug in scope_identity()and @@identity- see MS Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811
请注意，有中的错误scope_identity()和@@identity-见MS连接：https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811
A quote (from Microsoft):
引用（来自微软）：
I highly recommend using
OUTPUTinstead of@@IDENTITYin all cases. It's just the best way there is to read identity and timestamp.
我强烈建议使用
OUTPUT而不是@@IDENTITY在所有情况下。这只是读取身份和时间戳的最佳方式。
Edited to add: this may be fixed now. Connect is giving me an error, but see:
编辑添加：现在可能已修复。Connect 给我一个错误，但请参阅：
回答by JeremyWeir
There is almost no reason to use anything besides an OUTPUTclause when trying to get the identity of the row(s) just inserted. The OUTPUT clause is scope and table safe.
OUTPUT在尝试获取刚刚插入的行的标识时，几乎没有理由使用除子句之外的任何内容。OUTPUT 子句是范围和表安全的。
Here's a simple example of getting the id after inserting a single row...
这是插入单行后获取 id 的简单示例...
DECLARE @Inserted AS TABLE (MyTableId INT);
INSERT [MyTable] (MyTableColOne, MyTableColTwo)
OUTPUT Inserted.MyTableId INTO @Inserted
VALUES ('Val1','Val2')
SELECT MyTableId FROM @Inserted
Detailed docs for OUTPUT clause: http://technet.microsoft.com/en-us/library/ms177564.aspx
OUTPUT 条款的详细文档：http: //technet.microsoft.com/en-us/library/ms177564.aspx
-- table structure for example:
CREATE TABLE MyTable (
MyTableId int NOT NULL IDENTITY (1, 1),
MyTableColOne varchar(50) NOT NULL,
MyTableColTwo varchar(50) NOT NULL
)
回答by jcollum
回答by Cade Roux
SCOPE_IDENTITY is sufficient for single rows and is recommended except in cases where you need to see the result of an intermediate TRIGGER for some reason (why?).
SCOPE_IDENTITY 对单行就足够了，除非出于某种原因（为什么？）您需要查看中间 TRIGGER 的结果，否则建议使用。
For multiple rows, OUTPUT/OUTPUT INTO is your new best friend and alternative to re-finding the rows and inserting into another table.
对于多行，OUTPUT/OUTPUT INTO 是您最好的新朋友，也是重新查找行并插入另一个表的替代方法。
回答by Seibar
There is another method available in SQL Server 2005 that is outlined in SQL in the Wild.
在 SQL in the Wild 中概述了 SQLServer 2005 中的另一种方法。
This will allow you to retrieve multiple identities after insert. Here's the code from the blog post:
这将允许您在插入后检索多个身份。这是博客文章中的代码：
Create Table #Testing (
id int identity,
somedate datetime default getdate()
)
insert into #Testing
output inserted.*
default values
回答by Paul Sasik
A small correction to Godeke's answer:
对 Godeke 的回答稍作修正：
It's not just triggers you need to worry about. Any kind of nested operation, such as stored procs, that causes identifiers to be created could change the value of @@IDENTITY.
您需要担心的不仅仅是触发因素。任何导致创建标识符的嵌套操作（例如存储过程）都可能更改 @@IDENTITY 的值。
Another vote for scope_identity...
对 scope_identity 的另一票...
回答by Pato
Be carreful while using @@IDENTITY ...
使用@@IDENTITY 时要小心...
http://dotnetgalactics.wordpress.com/2009/10/28/scope-identity-vs-identity/
http://dotnetgalactics.wordpress.com/2009/10/28/scope-identity-vs-identity/
