Featured Database Articles

This is part 18 of a series. If you have not read part 17,
you can find it here.
That article is a good introduction to SQL Server 2005s OUTPUT clause.

What Were Testing

Part 17 introduced SQL Server 2005s OUTPUT clause. Here, we
will look at multiple ways to do the same thing. The scenario is that you have
a pre-loaded staging table, and you need to load a destination table and a
change-tracking history table. If there are n records in the staging
table, you will load n records to the destination table and n
records to the history table.

We will test multiple ways to accomplish this, two of which
use the OUTPUT clause.

The Tables

For this test, weve made two main changes to the basic
design from Part 17: added more columns and changed all tables to regular
tables (not variables). There are now 15 bit columns. The only table that
remains a table variable is the OUTPUT table variable (@insertedSet). Weve also created two versions (Base
and Trig) of the destination table so that the trigger added to DestinationTableTrig will not affect DestinationTableBase. The history
tables also have two versions (Base and Trig) to allow a foreign key from
the history table to the destination table.

This inserts the whole set of records from the staging table
within an implicit transaction and then writes the history table records from
the OUTPUT table variable.

Test 1b: Set-Based INSERTs Using OUTPUT (TRAN)

Test 1a but with a BEGIN TRAN/COMMIT wrapping the two INSERT
statements.

Test 2: Set-Based INSERTs Using an INSERT Trigger

For the second test, we add an INSERT/UPDATE trigger to the
destination table (DestinationTableTrig).
This simple trigger inserts into the history table based on the data inserted
into the destination table. The guts of the trigger look like this:

Test 3: Iterative INSERTs Using Straight SQL

For the third test, were using very 100-level SQL. A real
beginner would probably bring up BOL and struggle through running this in a
cursor, but using a counter and a WHILE loop is so much less painful. Heres
the basic plan:

Notice that the table variable retains its records from one
call to the next. It makes perfect sense once you think about it, and it is the
correct behavior, but you have to be aware of it when using the OUTPUT clause.
You may need to clear the table variable at times to make sure it is empty
before doing the next INSERT INTO ...
OUTPUT ... INTO <tableVariable> call. I had to do that
between calls in the test script.

The code for this simple test is in the attached HelloWorld_InsertMultiple.sql
file.

Conclusion

The OUTPUT clause is very useful for pulling automatic
values out of an INSERT statement (or UPDATE). What I was looking at here was
how it performs compared to other options.

To no ones surprise, the set-based solutions are far
superior to the record-at-a-time solutions. As more and more records are
involved, the record-at-a-time solutions get even worse.

It is important to see that wrapping the creation of the
base records and the history records is faster using an explicit transaction
(whether coded directly or wrapped in a stored procedure). It is also important
to note that none of these solutions is as fast as a set-based trigger.

Many SQL Server developers believe that triggers are
generally evil, and I tend to fall into that camp. There are two places where I
think triggers are appropriate. One is the creation of history records,
which is the exact case we have here. The other is for directly populating
tables that are used to populate a data warehouse.

So, after building and optimizing the OUTPUT clause, the
best-performing option for this particular scenario is a trigger. Whether you
think its evil or not, it does perform well.

I may have missed some optimization here. Do you see
something that would make the OUTPUT clause work as fast as the trigger? If so,
please drop a comment in the forum