Using the OUTPUT Clause in SQL Server

The OUTPUT clause in SQL Server 2008 was probably one of the most functional T-SQL enhancements added. I personally don’t use it enough because I often forget about it, however I have used it to overcome some serious deadlock incidents.

It basically works in conjunction with INSERT, UPDATE, or DELETE. In my opinion it will probably be most utilized in an update statement, however I’m sure there are many scenarios I may be forgetting. The way it works is while performing one of these statements, you have the option to output any data within the rows that are being INSERTED UPDATED or DELETED — into another table. This is important because it allows only a single pass through on the table. Whereas before when performing an update to a table, in order to get in-row data you would have to perform a separate select. This could especially become a problem if the update and select needed to be within a transaction. But the output clause is transactional by nature because it occurs within a single statement.

Let’s look at an example:

[cc lang=”sql”]
DECLARE @customer_id int = 1234;

DECLARE @customer table
(
first_name varchar(50),
last_name varchar(50),
phone_number varchar(50),
visit_count int;
);

UPDATE c
SET
customer_visit_count += 1
OUTPUT
DELETED.first_name,
DELETED.last_name,
DELETED.phone_number,
INSERTED.customer_visit_count
INTO @customer
FROM [customer] c
WHERE c.customer_id = @customer_id;

SELECT
first_name,
last_name,
phone_number,
visit_count
FROM @customer;
[/cc]

This allows a single query (and one transaction) to be used in order to update and select from a table. The DELETED keyword displays any values that existed prior to the operation being performed, whereas the INSERT keyword outputs the new values that were written.