SqlCredit - Part 17: Exploring SQL 2005's OUTPUT Clause

July 25, 2008

This is part 17 of a series. If you have not read part 16, you can find it here, but this article does not require any knowledge of the previous work in the series.

The OUTPUT Clause: Removing One More Reason to Run Per-Record Processing

Database people love to do things in sets. Occasionally, they are forced to process one record at a time, but they don’t like it.

In my previous job, I remember writing code that created multiple payment records. These payment records had change history records, and the creation of a record required a corresponding history record. It was something like this:

  Create Payment record
  Get ID value using SCOPE_IDENTITY()
  Create PaymentHistory record

I was convinced that I had to use per-record processing because I needed the identity value to create the history record. That was actually true at the time I originally wrote this code because I was writing it to work against SQL Server 2000.

However, SQL Server 2005 introduced the OUTPUT clause. This bit of magic allows us to get the identity value, plus a number of other values, from an INSERT, UPDATE, or DELETE. INSERT, UPDATE, and DELETE all have different interaction with OUTPUT. I will concentrate on INSERT. The attached code includes examples of all three.

The “Hello World” of OUTPUT Clause

Here is the simplest possible illustration of how the OUTPUT clause works.

  DECLARE @t1         TABLE (ColID tinyint IDENTITY, Col1 varchar(11))
  DECLARE @t1inserted TABLE (ColID tinyint,          Col1 varchar(11))
  
  INSERT INTO @t1
  OUTPUT Inserted.ColID, Inserted.Col1 INTO @t1inserted
  VALUES ('Hello World')

The contents of the two tables match exactly.

  @t1
  ColID Col1
  ----- -----------
  1     Hello World
  
  @t1inserted
  ColID Col1
  ----- -----------
  1     Hello World

It would be very reasonable at this point to ask, “So what?” The short answer is that you have access to the identity value of the inserted record by use the OUTPUT clause. The value of this will become clearer in the following scenario where we are inserting a set of records.

The Base Table

This code is all very simple. It uses nothing but table variables, so you can run it anywhere. It doesn’t require any existing database or anything.

The base table here takes four bits and calculates the number represented by those bits. For example,

  X8    X4    X2    X1    ComputedNum
  ----- ----- ----- ----- -----------
  1     0     1     0     10

@baseTable schema:

  DECLARE @baseTable TABLE (
      ColID       int                 NOT NULL
          IDENTITY,
      X8          bit                 NOT NULL,
      X4          bit                 NOT NULL,
      X2          bit                 NOT NULL,
      X1          bit                 NOT NULL,
      ComputedNum AS
          ((X8 * 8) + (X4 * 4) + (X2 * 2) + (X1 * 1)),
      CreateDate  datetime            NOT NULL
          DEFAULT GETDATE(),
      ColGUID     uniqueidentifier    NOT NULL
          DEFAULT NEWSEQUENTIALID()
  )

A couple of important things to note:

• This table takes just four bits as regular columns.
• The rest are all “automatic columns”: an identity column, a computed column, and two columns with defaults.

The “History” Table

The history (or “change-tracking”) table is similar. Yes, I understand that you would not track changes to such a simple table, but it works for illustration.

  DECLARE @historyTable TABLE (
      HistoryID         int              NOT NULL  IDENTITY(-1, -1),
      SourceID          int              NOT NULL,
      ComputedNum       int              NOT NULL,
      SourceCreateDate  datetime         NOT NULL,
      SourceGUID        uniqueidentifier NOT NULL,
      HistoryCreateDate datetime         NOT NULL  DEFAULT GETDATE()
  )

This table has four regular columns that receive values from @baseTable and two “automatic columns”. It does not include the individual bit columns from @baseTable.

INSERTing with an OUTPUT Clause

The INSERT:

  INSERT INTO @baseTable (
      X8,
      X4,
      X2,
      X1
  )
  OUTPUT
      Inserted.ColID,
      Inserted.ComputedNum,
      Inserted.CreateDate,
      Inserted.ColGUID
  INTO @insertedSet
  SELECT
      X8,
      X4,
      X2,
      X1
  FROM       (SELECT 0 AS X8 UNION SELECT 1) AS t1
  CROSS JOIN (SELECT 0 AS X4 UNION SELECT 1) AS t2
  CROSS JOIN (SELECT 0 AS X2 UNION SELECT 1) AS t3
  CROSS JOIN (SELECT 0 AS X1 UNION SELECT 1) AS t4
  ORDER BY t1.X8, t2.X4, t3.X2, t4.X1

The Populating SELECT Statement

The SELECT statement used to populate @baseTable uses CROSS JOIN to create the full set of possible bit values for these columns.

The OUTPUT Clause

The OUTPUT clause allows us to “record” any of the columns inserted into the base table, whether regular columns or automatic columns. In this code, all I am recording is the automatic columns. Understand that it is not necessary to OUTPUT any columns that you don’t plan to use.

INSERTing From the OUTPUT to the History Table

The history table INSERT code:

  INSERT INTO @historyTable (
      -- HistoryID
      SourceID,
      ComputedNum,
      SourceCreateDate,
      SourceGUID
      -- HistoryCreateDate
  )
  SELECT
      ColID,
      ComputedNum,
      CreateDate,
      ColGUID
  FROM @insertedSet
  ORDER BY ColID

I added a WAITFOR of one second between the initial insert and the history insert so that GETDATE() would return different values.

The Results

After running the complete script, here are the results (with some editing for width):

  @baseTable
  ColID X8 X4 X2 X1 ComputedNum CreateDate   ColGUID
  ----- -- -- -- -- ----------- ------------ ----------
  1     0  0  0  0  0           11:30:54:417 565...89DB
  2     0  0  0  1  1           11:30:54:417 575...89DB
  3     0  0  1  0  2           11:30:54:417 585...89DB
  4     0  0  1  1  3           11:30:54:417 595...89DB
  5     0  1  0  0  4           11:30:54:417 5A5...89DB
  6     0  1  0  1  5           11:30:54:417 5B5...89DB
  7     0  1  1  0  6           11:30:54:417 5C5...89DB
  8     0  1  1  1  7           11:30:54:417 5D5...89DB
  9     1  0  0  0  8           11:30:54:417 5E5...89DB
  10    1  0  0  1  9           11:30:54:417 5F5...89DB
  11    1  0  1  0  10          11:30:54:417 605...89DB
  12    1  0  1  1  11          11:30:54:417 615...89DB
  13    1  1  0  0  12          11:30:54:417 625...89DB
  14    1  1  0  1  13          11:30:54:417 635...89DB
  15    1  1  1  0  14          11:30:54:417 645...89DB
  16    1  1  1  1  15          11:30:54:417 655...89DB
  
  @insertedSet
  ColID       ComputedNum CreateDate   ColGUID
  ----------- ----------- ------------ ----------
  1           0           11:30:54:417 565...89DB
  2           1           11:30:54:417 575...89DB
  3           2           11:30:54:417 585...89DB
  4           3           11:30:54:417 595...89DB
  5           4           11:30:54:417 5A5...89DB
  6           5           11:30:54:417 5B5...89DB
  7           6           11:30:54:417 5C5...89DB
  8           7           11:30:54:417 5D5...89DB
  9           8           11:30:54:417 5E5...89DB
  10          9           11:30:54:417 5F5...89DB
  11          10          11:30:54:417 605...89DB
  12          11          11:30:54:417 615...89DB
  13          12          11:30:54:417 625...89DB
  14          13          11:30:54:417 635...89DB
  15          14          11:30:54:417 645...89DB
  16          15          11:30:54:417 655...89DB
  
  @historyTable
  HistoryID SourceID ComputedNum SourceCreateDate SourceGUID HistoryCreateDate
  --------- -------- ----------- ---------------- ---------- -----------------
  -1        1        0           11:30:54:417     565...89DB 11:30:55:417
  -2        2        1           11:30:54:417     575...89DB 11:30:55:417
  -3        3        2           11:30:54:417     585...89DB 11:30:55:417
  -4        4        3           11:30:54:417     595...89DB 11:30:55:417
  -5        5        4           11:30:54:417     5A5...89DB 11:30:55:417
  -6        6        5           11:30:54:417     5B5...89DB 11:30:55:417
  -7        7        6           11:30:54:417     5C5...89DB 11:30:55:417
  -8        8        7           11:30:54:417     5D5...89DB 11:30:55:417
  -9        9        8           11:30:54:417     5E5...89DB 11:30:55:417
  -10       10       9           11:30:54:417     5F5...89DB 11:30:55:417
  -11       11       10          11:30:54:417     605...89DB 11:30:55:417
  -12       12       11          11:30:54:417     615...89DB 11:30:55:417
  -13       13       12          11:30:54:417     625...89DB 11:30:55:417
  -14       14       13          11:30:54:417     635...89DB 11:30:55:417
  -15       15       14          11:30:54:417     645...89DB 11:30:55:417
  -16       16       15          11:30:54:417     655...89DB 11:30:55:417

Notice that the values of @historyTable.SourceCreateDate and @historyTable.HistoryCreateDate are exactly one second apart. This is due to the WAITFOR in the script.

Feedback

Specific questions for discussion in the forum:

• How have you used the OUTPUT clause? What are your lessons-learned?
• Is there a different way to retrieve values like GUIDs from a set-based INSERT?

For Next Time

How does the use of OUTPUT affect performance? Is set-based work really that much faster?

» See All Articles by Columnist Rob Garrison