What is the use of OUTPUT clause in sql server

Question

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;

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. .

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

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?

UPDATE with non-functioning 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

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

Answer 1

When you perform Insert/Update/Delete operation on particular table and want to know what rows are affected OR want to log them for audit trail OR you want to use multiple values of affected rows in subsequent sql statements, you can use OUTPUT clause.

For Insert statement, it will have INSERTED table.

For Delete statement, it will have DELETED table. In case of Update DELETED table will contain rows (with old values) before update operation performed.

For Update statement, it will have DELETED and INSERTED tables. DELETED table will contain rows (with old values) before update operation performed. INSERTED table will contain rows (with new values) after update operation performed.

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;

Now your query inserts rows in Production.ScrapReason as well as table variable @MyTableVar. Later it selects inserted rows from Production.ScrapReason and @MyTableVar. Thus you can compare both the resultset and it must have identical rows (considering Production.ScrapReason is empty table.)

I hope it makes sense!

Edit:

Inserted/Deleted tables will be available with Insert/Update/Delete statement and not after that. You may want to store those magic table values in db table or temp table.

Answer 2

Without the OUTPUT clause, how would you know which rows were deleted? Your example seems so simple because you already know the Id values, but what if you did this:

DELETE FROM T WHERE SomeColumn LIKE 'SomePattern%'

And you want to find out what was deleted. That's the purpose of the OUTPUT clause.

Answer 3

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'

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'

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()

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

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

or

INSERT X (Name) SELECT OtherName FROM Y

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.

You will need these IDs for example when creating dependent records with foreign keys. Think "Order" and "OrderDetails" which are linked by an OrderID column with an IDENTITY clause. Again, with a single INSERT you can get away with using SCOPE_IDENTITY() or @@IDENTITY, but when inserting multiple orders at once, you will need OUTPUT.