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. You can use this technique to prevent the unnecessary
reading of your online transaction processing (OLTP) tables or to implement
custom data auditing without the use of triggers.

A trigger primer

SQL Server 2005 includes two flavors of triggers:
data-definition language (DDL) and data-manipulation language (DML). To learn
how to use DDL triggers to create a powerful server or database auditing
solution, see my articles on using SQL Server
2005 to document Sarbanes-Oxley compliance and creating your own
database audit trail. DML triggers are executed in response to data-manipulation
statements and allow you to track any data change in your database.

How it works

Like DML triggers, the OUTPUT clause makes use of the
INSERTED and DELETED tables. When a data modification statement occurs in the
database engine, SQL Server 2005 will create the INSERTED and DELETED tables
and populate them with the data that has been modified. The structure of these
tables will be identical to the table structure for which the data was altered.
For delete statements, only the DELETED table will be populated and contain the
rows that were deleted from the user-defined table. Likewise for insert
statements—only the INSERTED table will be populated. However, for update
statements, the DELETED table will contain the data values prior to the update, and the INSERTED table will contain pre-update data.

Benefits: In with the OUTPUT

Using the new OUTPUT clause, you can save the altered data
to a temporary table or table variable for later use, eliminating the need to
access the original table.

Let’s assume that your application uses a transaction
composed of three statements that modify customer data; the database and your
company relies upon this application to process sales information. Due to an
abundance of recent sales, performance has degraded. To illustrate, consider
the following sales transaction:

  1. Sales
    are made to five new customers. Information for each customer is added to
    the Customers table.
  2. For
    the sales representatives who sold product to these five customers, their
    commission figures are updated.
  3. Add five
    new records to your notification system to send thank-you letters to these
    customers.

Operations two and three rely upon the information inserted
in operation one to accomplish their tasks. For example, you cannot credit your
sales representatives for their new sales unless you are able to identify the
customer to which they have sold product.

Because your sales team has been very successful lately, the
Customers table has grown by 200% and is heavily accessed by other enterprise
applications. Even if this table is indexed properly, it is not one you will
want to have to read in every transaction your application requires.

To eliminate unnecessary database reads, use the OUTPUT
clause to add these records to a table variable. In steps two and three, join
your tables with this table variable rather than the Customers table. You can
potentially save a tremendous amount of buffer memory and I/O by using this
technique.

Note: Microsoft
does not recommended using table variables with large data-sets. If you find
that your application requires using the OUTPUT clause to capture a large
amount of data, use a temporary table instead. Temporary tables live in the
tempdb database and allow for indexing.

Give it a try

In the example above, we looked at the idea of storing
records that have been inserted into the Customers table using the OUTPUT
statement and using that data in later transactions. Run the script in
Listing A to set up the data structures
we need for our example.

Now that we’ve created our table structure, we can take a
look at how the OUTPUT statement works. Because table variables reside in SQL
Server 2005’s memory and are discarded when the execution context has ended, you’ll need to run the script in
Listing B
at the same time.

Our code declares a table variable and inserts one record
into the Customers table. In addition to the insert statement, we are also
using the INSERTED table to add the new record values into our table variable. At
this point, you can use the table variable in place of the Customers table in
steps two and three in our application.

A moment to reflect

The new OUTPUT feature in SQL Server 2005 gives you a lot of
new options for designing applications. The ability to access the data that is
being changed without requiring another database call allows you to optimize
your application. As an added benefit, you can use the OUTPUT clause to create
custom auditing without the use of DML triggers.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.