Composable DML

Microsoft SQL Server 2005 introduced the OUTPUT clause that allows a modification statement to return modified rows as output to the caller or into a table. The OUTPUT clause allows you to specify which attributes from the modified rows to return, but not which rows. In other words, you cannot further filter the rows returned by the OUTPUT clause; rather you will get an output row for each modified row. If you need only a subset of the rows for some purpose, you have to direct all output rows into a table using the INTO clause, then query the table and filter only the desired rows. The problem with this approach is that it can be very expensive if the number of rows returned by the OUTPUT clause is very large, and you need only a small subset.

Microsoft SQL Server 2008 introduces support for composable DML. You can define a table expression based on a modification statement with an OUTPUT clause, and have an outer INSERT SELECT statement that queries the table expression, and populates a target table with the query results. The outer query can have a filter, and this way you can populate the target table with only the desired subset of rows returned by the OUTPUT clause.

To demonstrate the use of composable DML, first run the following code which creates the tables MyProducts and ProductsAudit in the AdventureWorks database and populates the MyProducts table with sample data from the Production.Product table:

USE AdventureWorks;

IF OBJECT_ID('dbo.MyProducts', 'U') IS NOT NULL

  DROP TABLE dbo.MyProducts;

SELECT * INTO dbo.MyProducts FROM Production.Product;

ALTER TABLE dbo.MyProducts ADD PRIMARY KEY(ProductID);

IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL

  DROP TABLE dbo.ProductsAudit;

CREATE TABLE dbo.ProductsAudit

(

  AuditID   INT         NOT NULL IDENTITY PRIMARY KEY,

  DT        DATETIME    NOT NULL DEFAULT(CURRENT_TIMESTAMP),

  ProductID INT         NOT NULL,

  ColName   SYSNAME     NOT NULL,

  OldVal    SQL_VARIANT NOT NULL,

  NewVal    SQL_VARIANT NOT NULL

);

The following code shows an UPDATE statement that increases the list price of all products by 15 percent. The OUTPUT clause is used to return the product IDs as well as the old and new list prices of the modified products. The code defines a derived table called D based on the UPDATE statement, and with an outer INSERT SELECT statement inserts into the audit table modified rows where the list price was under 30.0 before the change, and greater than or equal to 30.0 after the change:

INSERT INTO dbo.ProductsAudit(ProductID, ColName, OldVal, NewVal)

  SELECT ProductID, N'ListPrice', OldVal, NewVal

  FROM (UPDATE dbo.MyProducts

          SET ListPrice *= 1.15

        OUTPUT

          inserted.ProductID,

          deleted.ListPrice AS OldVal,

          inserted.ListPrice AS NewVal) AS D

  WHERE OldVal < 30.0 AND NewVal >= 30.0;

SELECT * FROM dbo.ProductsAudit;

The SELECT statement against the ProductsAudit table returns the following output:

AuditID  DT                      ProductID   ColName    OldVal  NewVal

-------- ----------------------- ----------- ---------- ------- ----------

1        2008-03-03 11:40:37.897 908         ListPrice  27.12   31.188

2        2008-03-03 11:40:37.897 911         ListPrice  27.12   31.188

3        2008-03-03 11:40:37.897 914         ListPrice  27.12   31.188

4        2008-03-03 11:40:37.897 929         ListPrice  29.99   34.4885

5        2008-03-03 11:40:37.897 934         ListPrice  28.99   33.3385

Currently, the outer statement in a composable DML statement must be INSERT SELECT, and the only clause allowed in it is the WHERE clause. The inner statement can be INSERT, UPDATE, DELETE or MERGE with an OUTPUT clause.

When you’re done, run the following code for cleanup:

DROP TABLE dbo.MyProducts;

DROP TABLE dbo.ProductsAudit;

Cheers,

BG