sql server中OUTPUT子句的用途是什么

What is the purpose of the OUTPUT clause? I have gone through the MSDN documentation for the OUTPUT clause, which includes the following example:

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

从上面的查询来看，删除的记录似乎保存在一个名为deleted的魔术表中，查询会将这些记录从魔术deleted加载到名为MyTableVar的表中> 桌子..

From the above query, it seems that deleted records are saved in some magic table called deleted, and the query will load those records into table called MyTableVar from the magic deleted table. .

我还是不明白 OUTPUT 子句用法的目的.

I still do not understand the purpose of the OUTPUT clause usage.

作为另一个 SQL 示例:

As another SQL example:

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

这实际上是在做什么?谁能用一个简单的例子解释这个子句的作用?

What is this actually doing? Can anyone explain what this clause is doing with an easy example?

create proc test
as
CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
)
go

INSERT INTO dbo.table1 VALUES 
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice')
delete from table1
select * from deleted

这在我运行时给我一个错误，因为它看不到 deleted 表.

This gives me an error when I run it, because it can't see the deleted table.

推荐答案

该子句的一般目的是捕获对数据所做的更改，而无需额外的查询，这会引入锁定和阻塞问题.示例:

The general purpose of this clause is to capture the changes made to your data without an additional query, which would introduce locking and blocking issues. Example:

DELETE FROM X WHERE Name = 'Foo'

您想知道删除了哪些 ID.你可以像这样天真地这样做:

You want to know which IDs were deleted. You can do this naively like this:

SELECT ID FROM X WHERE Name = 'Foo'
DELETE FROM X WHERE Name = 'Foo'

但是这些选定的 ID 是不可靠的，除非您在隔离级别为 SERIALIZABLE 的事务中运行，但通常情况并非如此.其他人可以在您的两个语句之间添加、删除或更改Foo"-Records.因此，您可以使用 OUTPUT 子句并准确可靠地取回已删除的 ID，而不会出现任何性能或可靠性问题.

But these selected IDs are unreliable unless you are running in a transaction with isolation level SERIALIZABLE which is usually not the case. Someone else can add, delete or change "Foo"-Records between your two statements. So instead you can use the OUTPUT clause and get back exactly and reliably the deleted IDs without any performance or reliability issues.

另一个经常使用的是获取插入的默认值的值，尤其是在使用标识列时.对于单个插入，您可以这样做:

Another frequent use is to get the value of inserted default values, especially when using identity columns. For a single insert you can do this:

CREATE TABLE X
 (
 ID INT IDENTITY,
 Name VARCHAR(10)
 );

INSERT X (Name) VALUES ('Foo')

SELECT SCOPE_IDENTITY()

但是 SCOPE_IDENTITY() 只能给你最后插入的 ID.如果您进行多次插入，例如

But SCOPE_IDENTITY() can give you only the last inserted ID. If you do multiple inserts, like

INSERT X (Name) VALUES ('Foo'), ('Bar')

或

INSERT X (Name) SELECT OtherName FROM Y

如果你想知道插入的 ID，你就不走运了.您可以尝试使用另一个 SELECT 找到它们，但是您甚至需要另一个唯一的列来制定查询，然后您会遇到与上面的 DELETE 示例相同的问题.因此，OUTPUT 子句可以让您清楚地识别哪些 Names 获得了哪些 ID.

and you want to know the inserted IDs, you are out of luck. You can try to find them with another SELECT, but you need another unique column to even formulate the query and then you run into the same issues as with the DELETE sample above. So, the OUTPUT clause lets you identify neatly which Names got which IDs.

例如，在使用外键创建相关记录时，您将需要这些 ID.想想订单"和订单详细信息"，它们由带有 IDENTITY 子句的 OrderID 列链接.同样，通过一次 INSERT，您可以避免使用 SCOPE_IDENTITY() 或 @@IDENTITY，但是当一次插入多个订单时，您将需要 OUTPUT.