Output Clause:

Output is a new clause which can be paired off with Insert, Update & Delete statements to get the before and after image of the date being modified. It basically uses 2 magic tables
called as Inserted, Deleted for its operation.

Inserted table would contain a row for each row that is inserted and deleted table would contain a row each row that is deleted whereas both inserted and deleted would contain 1
row for row which is updated where the deleted holds the pre-image of data and inserted holds the new data that is inserted.

For Ex:
Col1 Col2
1 Peter

So if I update Peter to Peter Drucker then inserted table would have(Post Image)
Col1 Col2
1 Peter Drucker

And Deleted table would have(pre-image)
Col1 Col2
1 Peter

This feature was basically available only inside Triggers in SQL 2000, but however in SQL 2005 it is exposed and can be used even inside a stored procedure.

The ability to immediately access data changes without requiring additional reads from the database can enhance application throughput. When data modification statements
are executed in SQL Server 2005, you can capture the data that has been modified and store it for use in subsequent transactions.

One can use this technique
ü Trips to database from .Net or Java application to the database is reduced as its combined with INSERT/UPDATE/DELETE
ü To prevent the unnecessary reading of your online transaction processing (OLTP) tables which enhances application performance.
ü To implement custom data auditing without the use of triggers.

Let us see some examples on how to use various OUTPUT clauses.

Using Output clause to return the last value inserted into Identity, which is possible only by means of executing @@IDENTITY clause in SQL 2000

CREATE TABLE [dbo].[DOCS_HISTORY]
( [DOC_ID] [int] IDENTITY(1,1) NOT NULL,
[DOC_NAME] [varchar](100) )

INSERT INTO [DOCS_HISTORY]
OUTPUT INSERTED.[DOC_ID],INSERTED.[DOC_NAME]
SELECT 'test docs'

Using Output and Output INTO to show output and capture befor and after image to audit table

DECLARE @Tbl_Docs TABLE
( [DOC_ID_NEW] INT
, [DOC_NAME_NEW] VARCHAR(100),
[DOC_ID_OLD] INT ,
[DOC_NAME_OLD] VARCHAR(100),
CR_DATE DATETIME)

UPDATE [DOCS_HISTORY]
SET DOC_NAME = 'test documents'
OUTPUT INSERTED.[DOC_ID],
INSERTED.[DOC_NAME],
DELETED.[DOC_ID],
DELETED.[DOC_NAME],
GETDATE()
INTO @Tbl_Docs
OUTPUT INSERTED.[DOC_ID],
INSERTED.[DOC_NAME],
DELETED.[DOC_ID],
DELETED.[DOC_NAME]
WHERE DOC_ID = 100001

This is a very useful feature for auditing the pre and post image of data which was only possible by means of trigger in SQL 2000

Similarly OUTPUT can be used with DELETE statement as well to capture the data that was deleted from DELETED table.