Systems Engineering and RDBMS

Enhancements to the OUTPUT clause in SQL Server 2008

Posted by decipherinfosys on May 28, 2008

We had covered the OUTPUT and the OUTPUT INTO clauses in SQL Server 2005 before in this post. Prior to SQL Server 2008, as you can see from the examples from that post, one could not filter out the records that are returned using the OUTPUT/OUTPUT INTO clauses i.e. one can choose only the different columns that one wants in those clauses but cannot choose which rows one wants to return. As a result, one has to dump all of that data into a table and then apply a filter criteria (WHERE clause) at that table in order to sub-select the records that one wants to get.

In SQL Server 2008, one of the new T-SQL enhancements makes this pretty simple. One can now define a table expression based off a modification statement with an OUTPUT clause and then apply the filters on it. For Example:

INSERT INTO dbo.PO_DTL (….)
SELECT ….
FROM (UPDATE dbo.INPT_PO_DTL
SET Quantity *= 1.5
OUTPUT
inserted.PO_DTL_ID,
deleted.Quantity AS Old_Qty,
inserted.Quantity AS New_Qty
WHERE RECVD_DATE > getdate() – 10) AS IV
WHERE IV.Old_Qty < 100.0 AND New_Qty >= 100.0
GO

One can also join that data set with another table and do processing.  The biggest advantage (besides not having another table and another set of steps to achieve the same thing is the fact that we are reducing the number of records that we have to retrieve – previously, if the data set returned would have been large, we would have first stored it in a temp table and then applied a filter on it.  If that data set was huge, it would have had performance implications.  With this new feature, we can easily reduce that overhead and get only the records that we want/need.

You can also see the new syntax of ” *= ” for the multiplication of the data – that is the compound assignment operator in SQL Server 2008.

One Response to “Enhancements to the OUTPUT clause in SQL Server 2008”

  1. […] clause usage is in that KB article and you can also read up more on it in our blog post here and here.  You can also opt for Method 2 mentioned in the workaround in the article.  Method 1 and Method […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: