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?
