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:
- Sales are made to five new customers. Information for each customer is added to the Customers table.
- For the sales representatives who sold product to these five customers, their commission figures are updated.
- 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 firstname.lastname@example.org.
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.