Composable DML

Composable DML is an extension SQL Server 2008 extension of the OUTPUT clause which one was originally published in SQL Server 2005. OUTPUT is generally returning all rows from the DML where it’s used without an option to filter the results set. Composable DML extends this concept for INSERT…SELECT scenarios and brings a limited option to filter/slightly modify the result set provided by the OUTPUT clause. Let’s see how it’s working.

We will create a table [dbo].[SampleTable] and fill it with some dummy data first.

Table [dbo].[TrackedChanges] will be used to store changes history from DML we will run againts [dbo].[SampleTable]:

We will run the main Composable DML statement now: It’s an UPDATE statement to change Product price followed by OUTPUT clause. That all is nested in INSERT…SELECT statement with WHERE filter condition to track changes in [dbo].[TrackedChanges] table only in case when the updated [Price] is over 300.

This query concept is mainly useful when handling SDC Type II in data warehouse scenarios.

Few limitations exist still and Composable DML is not fully done 10 years after publishing. I.e. we can’t use JOINs, TOP or GROUP BY clauses. Let’s demonstrate it on JOIN example:

Besides these limitations, I really like it and have a lot of fun when junior developers are scratching heads what the hell is it for peace of code:)

Leave a Reply

Your email address will not be published. Required fields are marked *