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.

-- Create sample table
CREATE TABLE [dbo].[SampleTable]
(
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [ProductName] VARCHAR(50) NOT NULL,
    [Price] MONEY NOT NULL
)
GO

-- Populate data
INSERT [dbo].[SampleTable] ([ProductName], [Price])
    SELECT 'Product1', 100 UNION ALL
    SELECT 'Product2', 200 UNION ALL
    SELECT 'Product3', 300
GO

-- Review sample data
SELECT [Id], [ProductName],	[Price] FROM dbo.[SampleTable]
GO

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

-- Create table for changes history
CREATE TABLE [dbo].[TrackedChanges]
(
    [RowId] INT,
    [OldValue] MONEY,
    [NewValue] MONEY,
    [ChangeDate] DATETIME DEFAULT (GETDATE())
)
GO

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.

-- Perform Composable DML
INSERT INTO [dbo].[TrackedChanges]
        ( [RowId], [OldValue], [NewValue] )
SELECT 
	[RowId], [OldValue], [NewValue]
FROM 
(
    UPDATE [dbo].[SampleTable]
        SET [Price] = [Price] * 1.5
    OUTPUT
        [INSERTED].[Id] AS [RowId],
        [DELETED].[Price] AS [OldValue],
        [INSERTED].[Price] AS [NewValue] 
) [upd] 
WHERE NewValue > 300
GO

-- Check results
SELECT [RowId], [OldValue], [NewValue],	[ChangeDate] FROM [dbo].[TrackedChanges]
GO

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:

INSERT INTO [dbo].[TrackedChanges]
        ( [RowId], [OldValue], [NewValue] )
SELECT 
	[RowId], [OldValue], [NewValue]
FROM 
(
    UPDATE [dbo].[SampleTable]
        SET [Price] = [Price] * 1.5
    OUTPUT
        [INSERTED].[Id] AS [RowId],
        [DELETED].[Price] AS [OldValue],
        [INSERTED].[Price] AS [NewValue] 
) [upd] 
	INNER JOIN [dbo].[SampleTable] st ON [st].[Id] = upd.Row_Id
WHERE NewValue > 300
GO

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:)