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
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.
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. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
