Tip

Using the OUTPUT clause for practical SQL Server applications

While the majority of articles describing new functionality in SQL Server 2005 focus on flashy features such as SQLCLR or the XML data type, many enhancements to the good old T-SQL language didn't get as much attention as they deserved. I've heard from a few DBAs and they were more excited about T-SQL enhancements than about the much promoted and publicized functionality. One useful enhancement to the actual SQL language is the OUTPUT clause, which

    Requires Free Membership to View

SQL Server 2005 features:

 allows you to retrieve the set of rows affected by a data modification command.

This article will describe how the OUTPUT clause feature works in SQL Server. I'll show you how, among other things, the OUTPUT clause is useful for easily satisfying business requirements for auditing and archiving data modifications.

The foundation of the OUTPUT clause is very simple -- it returns all the rows affected by an INSERT, UPDATE or DELETE command. The OUTPUT clause can return these rows to the client application, insert them into a permanent or temporary table and also insert the rows into a table variable. You simply attach the OUTPUT clause to an INSERT/UPDATE/DELETE statement.

In the OUTPUT clause, you can reference either the INSERTED or the DELETED virtual table, depending on whether you want to capture the data before it was modified (DELETED table) or after it was modified (INSERTED table). This is pretty much the same technique we use for triggers to work with modified data.

Note: You cannot reference DELETED in an INSERT statement and INSERTED in a DELETE statement because these virtual tables are only created by SQL Server when they logically make sense.

Now that we've covered the basics of the OUTPUT clause in SQL Server, let's look at some examples and practical SQL Server applications of this functionality. I'll start by creating a simple Employee table:

CREATE TABLE dbo.Employees
(
EmployeeID INT NOT NULL IDENTITY(1, 1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Status VARCHAR(20) DEFAULT 'Single'
)

Next, let's insert a row and append the OUTPUT to return the inserted row to the application that executes the insert:

INSERT INTO dbo.Employees ( FirstName, LastName )
OUTPUT INSERTED.*
SELECT 'Susan', 'Kelley'

EmployeeID FirstName LastName Status
1 Susan Kelley Single

As you can see, SQL Server returned the row inserted by the INSERT statement. This technique is useful for returning server-generated values such as identity columns or column defaults back to the application.
Next, we'll direct the output from an INSERT statement to a table using a real-life scenario. Let's say Susan gets married and changes her last name. In that case, we need to update her employee record. The company policy says we have to retain the historical data for all the employees, so we need to archive old employee data. We create a table called Employee_Archive with a couple of additional fields:

REATE TABLE dbo.Employees
(
EmployeeID INT NOT NULL IDENTITY(1, 1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
[Status] VARCHAR(20) DEFAULT 'Single'
ChangedBy VARCHAR(300) NOT NULL,
ChangedDatetime DATETIME NOT NULL
)

Now we can update Susan's record and at the same time insert the old version of the row in the Employee_Archive table using the OUTPUT clause and the DELETED virtual table:

UPDATE dbo.Employees
SET LastName = 'Jones',
Status = 'Married'
OUTPUT DELETED.*, system_user, getdate()
INTO dbo.Employees_Archive
WHERE EmployeeID = 1

After you execute this query, you will have this data in the Employee table:

EmployeeID FirstName LastName Status
1 Susan Jones Married

The Employee_Archive table contains the row with the old values plus the username and the time of the data change. This is the output on my computer; your output will differ slightly:

 

EmployeeID FirstName LastName Status ChangedBY ChangedDatetime
1 Susan Kelley Single rrehak 2008-04-21 02:04:18.310

Another practical use for the OUTPUT clause is to save a list of affected rows for additional processing. It will be very useful in scenarios where you need to update a set of rows, do something else and then again update the same set of rows -- something I do quite often when synchronizing data in two different databases using a set of INSERT/UPDATE/DELETE statements. Since you will have a list of IDs, you won't have to run the same query that gave you the set the first time. Instead, you can use these IDs in your WHERE clause, possibly avoiding a complicated and costly query to retrieve the same dataset. The following example creates a temporary table and saves the IDs of altered employee records:

CREATE TABLE #EmployeeIDs
(
EmployeeID INT NOT NULL
)
GO

UPDATE dbo.Employees
SET LastName = LastName
OUTPUT INSERTED.EmployeeID
INTO #EmployeeIDs

After the UPDATE statement, the temp table contains IDs of all modified rows.

The OUTPUT clause can actually save on processing time if you need to purge large amounts of data from a table into an archive table. Most experienced DBAs know that you should break up your delete operations into a batch of smaller deletes, maybe 100,000 rows or so. So the core of your purging code might look similar like this:

WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
INSERT INTO ArchiveTable
SELECT *
FROM MainTable
WHERE ID BETWEEN @MinID AND @MaxID

DELETE FROM MainTable
WHERE ID BETWEEN @MinID AND @MaxID
COMMIT TRANSACTION
END

If instead you use the OUTPUT clause, you can do it in one statement and save on processing time because the matching rows have to be located only once:

WHILE 1 = 1
BEGIN
DELETE FROM MainTable
OUTPUT DELETED.*
INTO ArchiveTable
WHERE ID BETWEEN @MinID AND @MaxID
END

As you can see, there are many options and possibilities for using the OUTPUT clause to simplify your code and eliminate triggers in some scenarios. I showed you how to use the OUTPUT statement for auditing or archiving your data, capturing a set of modified rows and simplifying your data purge routines.

ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

This was first published in May 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.