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 (….)
FROM (UPDATE dbo.INPT_PO_DTL
SET Quantity *= 1.5
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
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.