SQL201–The OUTPUT Clause
Starting with SQL 2005 SQL introduced a new clause for your INSERT, UPDATE, DELETE, and MERGE statements…the OUTPUT clause. If you’re not using it yet, it might be because you’re not sure what you could use it for. Let’s go through a scenario where the OUTPUT clause could save you from doing a RBAR (Row By Agonizing Row) operation.
INSERT a record and retrieve the IDENTITY created
I’m going to insert a record into AdventureWorks.Sales.SalesOrderDetail, and return the SalesOrderDetailID created by the INSERT.
Simple enough right?
What if I wanted to INSERT more than one record?
Well, if you only had access to SCOPE_IDENTITY() then you’d have to run row by row, inserting one at a time, and selecting the identities created. If you’re doing one or two records that might be fine. But as SQL developers you have to think in sets of thousands.
Considering INSERT statements with thousands of rows, you need to learn how the OUTPUT clause could show you all the identities created. Let’s use OUTPUT now.
We’re going to insert two rows into Sales.SalesOrder Detail.
The syntax for OUTPUT is pretty simple, you just add the clause between the INSERT INTO and the SELECT or VALUES clause. Then when you run it you get the identities inserted, right?
Msg 334, Level 16, State 1, Line 1 The target table 'sales.salesorderdetail' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
Close… But no cigar. This is the one catch with the OUTPUT clause. It’s not an option if your table already has a trigger. You could get away with an OUTPUT INTO clause, we’ll cover that option later. For now, I’ll just remind you you can DISABLE a TRIGGER.
And now you get the two identities you created with this insert statement.
That’s just the beginning.
Over the past few weeks, I’ve worked with developers to build queue tables and help them process records in that table. One process adds records to the queue. Another process picks up a couple thousand at a time, then does work based on that set. Before we were getting lots of locking and blocking and quite a few deadlocks.
But with some creative indexing and OUTPUT clauses, we were able to eliminate all the contention between the process that adds records to the queue, and the process that removes records from the queue.
I’ve also built archival processes using the OUTPUT clause. When it deletes from a table, the OUTPUT INTO clause puts those records into an archival table.
The OUTPUT INTO clause?
Yup. It’s just like the OUTPUT clause, except it puts the data into a table. The table can be a real table, or a table variable. Let’s take the output example from above, and shove it into a table variable called @InsertedIdentities.
Note that the INTO part of the OUTPUT INTO clause comes after the list of columns you’re trying to INSERT INTO the destination table.
Get to know the OUTPUT clause, it’s incredibly useful, especially when doing a scan of a table could result in reading through millions of rows to find out which ones you just updated. You can get the data out of the subset of rows you updated. A serious performance gain when you start talking VLDBs.
As always, if you have any questions, send them in…I’m here to help!