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
FROM
(UPDATE dbo.Students1
SET City = 'Bilaspur'
OUTPUT deleted.City AS 'oldvalue',
inserted.City AS 'newvalue'
WHERE SId=1) R;

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

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

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 🙂

Regards

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