Composable SQL, Have you heard it?

Hey Folks;

I am sure that many of you won’t even have heard about this topic. But don’t worry at all. I am here to give you some brief about it. Let first start with its evolution, i.e. why it is needed?

Earlier, with the release of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator functions much like a “local trigger” on the current statement. The drawback was that there was no way to filter the returned result-set directly. We have to insert the result-set in a staging table and work from there.

But with the arrival of SQL Server 2008 we now have a tool named Composable SQL also called Composable DML, which is being used to place the DML statements and its OUTPUT clause in a subquery and then select from that subquery.  So I will give you some heads up:

  • Composable SQL has the ability to pass data from an Insert, Update, Delete, or Merge output clause to an outer query. 
  • This is a very powerful innovative way to build Subqueries, and so it significantly reduces the amount of code needed and improves the performance of code that needs to write to one table, and then, based on that write, write to another table. 
  • SQL Server has DML triggers, which include inserted and deleted virtual tables, to track the evolution of Composable SQL. These are basically a view to the DML modification that fired the triggers. The deleted table holds the before image of the data, and the inserted table holds the after image of a data.
  • The primary benefit of composable SQL is that OUTPUT clause data may be further filtered and manipulated by the outer query.
  • This will become much more clear to you after you see this example:

First, Create a table with a name CompDML;

CREATE TABLE CompDML (oldvalue varchar(50), newvalue varchar(50));

Now, insert the values to these attribute using a subquery, which has an UPDATE command with an OUTPUT clause. The OUTPUT clause passes the oldvalue and newvalue columns to the outer query. So it can view as:

CREATE TABLE CompSQL (oldvalue varchar(50), newvalue varchar(50));
INSERT INTO CompSQL(oldvalue, newvalue)
SELECT oldvalue, newvalue
(UPDATE dbo.Students1
SET City = 'Bilaspur'
OUTPUT deleted.City AS 'oldvalue',
inserted.City AS 'newvalue'

The table which we going to be edit is Students1, it can be seen as:


1_SQL Server_Composable_SQL_Have_you_heard_it

Now, if we want to see the table CompSQL, which is being used to store the modified values and old values:

2_SQL Server_Composable_SQL_Have_you_heard_it

Composable SQL has few drawbacks:

  • The composable SQL query must include nested composable SQL, aggregate function, subquery, ranking function, full-text features, user-defined functions that perform data access, or the textptr function.
  • The target table must be a local base table with no triggers, no foreign keys, no merge replication, or updatable subscriptions for transactional replication.
  • The update DML in the subquery must modify a local table and cannot be a partitioned view.

Well this was all about Composable SQL.

Hope you like it 🙂



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.