Use the output clause in SQL Server Applications

Currently, most articles describing SQL Server 2005's new features focus on flashy features, such as sqlclr or XML data types, but the improvement of many good old T-SQL language has not received the due attention. I have heard from many DBAs that they are more excited by seeing improvements in the T-SQL language, rather than those new and released features. An output clause is a useful improvement in SQL. It allows you to query the record lines affected by a data modification command.

This article will discuss the specific application of the output clause in SQL Server. I will show you how to easily use the output clause to implement business requirements such as auditing and data modification archiving, as well as other related content.

 

The basic principle of the output clause is simple-it returns record lines affected by each insert, update, or delete command. Output can return these record rows in the client application, insert them into a persistent or temporary table, or insert the records into a table variable. It is used to directly attach the output clause to any insert/update/delete statement.

 

The output clause can reference the inserted or deleted virtual table, depending on whether you want to obtain data before (Deleted table) or after (inserted Table. This is similar to the operation of using a trigger to modify data.

 

Note: deleted cannot be referenced in an insert statement, nor inserted can be referenced in a deleted statement, because these virtual tables are meaningless logically in both cases, therefore, SQL server will not be created.

 

Now that we have understood the basic usage of the output clause in SQL Server, let's take a look at some of its examples and the practical application in SQL Server. I will start from creating a simple employee table:

 

Create Table DBO. Employee ( Employeeid int not null identity (1, 1 ), Firstname varchar (50) not null, Lastname varchar (50) not null, Status varchar (20) default 'single' )

 

Next, we need to insert a row of data and add output to return the records inserted into the application during the insert operation:

 

Insert into DBO. employee (firstname, lastname) Output inserted .* Select 'Susan ', 'kelley'

 

Employeeid	Firstname	Lastname	Status
1	Susan	Kelley	Single

 

We can see that SQL server returns the records inserted by the insert statement. This technique is useful for finding the value generated by the server and returning it to the application, such as marking the field or the default value of the field.

Next, we will insert the output from the insert statement into a table in real time. For example, Susan changed her surname after she got married. At this time, we need to update her employee information. Company policies require that we retain historical data for all employees, so we need to archive old employee data. In this way, we have created a table employee_archive and some additional fields:

Create Table DBO. employee_archive ( 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 the information record of Susan, and use the output clause and deleted virtual table to insert the old record rows into the employee_archive table:

 

Update DBO. Employee Set lastname = 'Jones ', Status = 'married' Output deleted. *, system_user, getdate () Into DBO. employee_archive Where employeeid = 1

After executing this query, we will get the data in the employee table:

 

Employeeid	Firstname	Lastname	Status
1	Susan	Jones	Married

 

The employee_archive table contains a record row with the old data value, user name, and data modification time. This is the output on my computer. The output on your computer may be somewhat different:

 

Employeeid	Firstname	Lastname	Status	Changedby	Changeddatetime
1	Susan	Kelley	Single	Rrehak	2008-04-21 02:04:18. 310

 

Another practice of the output clause is to save a series of affected records for subsequent processing. This is useful when updating a set of records, after some additional processing, update the same record set again. This is often done when I use a set of insert, update, and delete statements to synchronize two different databases. Because we have a column ID, we do not have to execute the first query again. On the contrary, we can use these IDs in the WHERE clause, so that we do not need to perform a complex and overhead query to retrieve the same dataset. In the following example, a temporary table is created and the ID of the modified employee record is saved:

 

Create Table # employeeids ( Employeeid int not null ) Go Update DBO. Employees Set lastname = lastname Output inserted. employeeid Into # employeeids

 

After the update statement is executed, the temporary table contains the ID and all the modification records.

 

If you need to clear a large amount of data from a table and store it in an archive table, the output clause can effectively save processing time. Most experienced DBAs split the delete operation into a group of smaller delete operations, which may be 100,000 or more. The code for deleting data may be as follows:

 

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 the output clause is used, we can complete this operation in a statement, and save a lot of processing time, because the configuration record line only needs to be performed once:

 

While 1 = 1 Begin Delete from maintable Output deleted .* Into archivetable Where ID between @ minid and @ maxid End

We can see that there are many possibilities for using the output clause to simplify code and replace triggers. This article describes how to use output statements to audit and archive data, obtain a group of modified record rows, and simplify data deletion procedures.