THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

The Transact-SQL Prime Directive


“There can be no interference with the flow of set-based data in Transact-SQL.” – the Prime Directive for the design of Transact-SQL


No, this is not from Microsoft, and of course, I made it up.


If you ever watched the TV series Star Trek, you may know the so-called Prime Directive, which is Starfleet’s General Order #1—the most prominent guiding principle of the United Federation of Planets. The Starfleet Prime Directive dictates that there can be no interference with the internal development of pre-warp civilizations.


That’s Star Trek. Now, this is about Transact-SQL. What is or should be the Prime Directive for the design of Transact-SQL?


I don’t know whether SQL Server Transact-SQL is developed under a certain prime directive, or a set of guiding principles. If it is, these principles are not published. If they are published, I have missed them.


Let me be clear. It is not necessarily a bad thing if a language like Transact-SQL is improved over time piecemeal in response to changing market conditions, customer feedbacks, and/or technology advances. However, I do believe it would be even better if it is also being developed in compliance with some sound guiding principles or a sound prime directive just as Starfleet’s space exploration is guided by its Prime Directive.


So if you were in the position to set in force a single most important guiding principle for the design of Transact-SQL (note I mean the design of Transact-SQL instead of the use of Transact-SQL), what would be your choice?


The Prime Directive

If I were to put in place the Transact-SQL Prime Directive, here would be mine: There can be no interference with the flow of set-based data in Transact-SQL.


Let’s take a look at why this rule should be elevated to the status of the prime directive for the design of Transact-SQL.


The fundamental nature of Transact-SQL

Transact-SQL, as compared to SQL, is basically SQL plus various control flow structures and some language constructs. They include, among others, the while loops, local variables, subroutines in the form of stored procedures for instance, and so on. The whole purpose of Transact-SQL minus SQL is to facilitate the use of SQL on the server side. To move a step further, I’d venture to state that its purpose is to facilitate the flow of the set-based data being processed by various SQL statements which are essentially set operators.


Now, if Transact-SQL started to interfere with the flow of the set-based data within SQL Server, it would have failed its very purpose.


What about the language design principles that we all know?

There is no doubt that you can come up with all sorts of language design principles with respect to such concepts of great interest as unambiguous and consistent formal language grammar, rigorous abstraction and modularity support, rigorous concurrency support, simplicity, regularity, and so on. They are well established, and we have studied them in the university computer science courses. If you pick up any decent software engineering textbook, you would find discussions on these principles. All important as they may be, they are principles that any formal language should abide by, and they are not particular to controlling SQL or the flow of set-base data.


If you can only select one principle as the prime directive for Transact-SQL language design, these software engineering principles come up short.


Does the current version of Transact-SQL violate this Prime Directive?

Well unfortunately, it does in a number of cases. Let me examine two common but prominent scenarios.


·      Restrictions on nested INSERT EXECUTE

Current version of Transact-SQL prohibits nested INSERT EXECUTE. In other words, you can’t insert the resultset produced by EXECUTE youProc into a table if there is already an INSERT EXECUTE in the procedure yourProc. The flow of set-based data is interfered with, or rather stopped in its track, and there is essentially no set-based workaround. The Prime Directive is violated.


Note that this restriction is there not because of any principled reason. It’s an implementation issue that should have been overcome should a prime directive was in place to right the priority.


·      Batch termination

In any other common languages, the behavior of batch termination would be considered a fatal bug that leads a program to an uncontrolled death. In other words, it would be a bug that crashes your program. In Transact-SQL, it is an accepted behavior by design.


The batch termination behavior is understandable given the status of Transact-SQL in the pantheon of languages. Unlike languages such as C, C++, Java, C#, and so on, Transact-SQL was never intended to be a full fledged and completely self-contained language. It was always a mini-language that was to live in the context of another language, that is, a client language. So when the floor caves in from within Transact-SQL such as when a batch is terminated while it is being processed, the expectation is that a client language would be there to hold it, and the system can continue from there without a complete crash.


The problem is that Transact-SQL has been routinely used, especially in many administrative scripts, as if it were a completely self-contained language without having a client language to pick up the pieces. In such a script, when a batch terminates, the flow of set-based data is stopped with no recourse for recovery. The Prime Directive is violated.


Bottom line is that if there were a Transact-SQL prime directive as I have stated, these two terrible and make-no-sense restrictions would have been lifted in top priority. Sadly, that’s obviously not the case. And there is no guarantee that we won’t run into another case where the flow of set-based data is stopped dead in its track in Transact-SQL.


How about facilitating the flow of set-based data?

You may be tempted to state that the prime directive for the design of Transact-SQL is to facilitate the flow of set-based data. Well, this is an excellent best-practice advice. But it’s rather subjective, difficult to measure and hard to enforce. After all, how do you determine whether Transact-SQL has violated it?


Sure, it would be nice to have some of the following constructs to facilitate the flow of set-based data (without any silly restrictions that may be there):



o       SELECT … FROM (INSERT …)

o       Or more generally, the ability to select from any language construct as long as it generates a well-defined resultset, i.e. set-based data, just like you can pipe text output in Unix/Linux or you can pipe objects in Powershell.


Now, although I see the addition of these language constructs as facilitating the flow of set-based data—which is of course a very good thing, I don’t consider their lack of as a violation of the Transact-SQL Prime Directive because you can always get around with the help of other constructs and still preserve the flow of set-based data in Transact-SQL.


What would be your choice of the Prime Directive for Transact-SQL?

That’s it. I’ve told you my choice. So again, if you were to set in force the prime directive for the design of Transact-SQL, what would be your choice?


Published Monday, September 21, 2009 9:47 AM by Linchi Shea



Jean-Luc Picard said:

Make it so!

September 21, 2009 11:38 AM

Mladen said:

i wonder what's the Omega Directive of T-SQL then?

it does override the Prime directive. :)

September 21, 2009 11:58 AM

Linchi Shea said:

Isn't the Omega Directive supposed to be more secret than top secret? Can't talk about it. :-)

September 21, 2009 1:02 PM

AaronBertrand said:

Rule #1 of Fight Club : you do not talk about Fight Club.

September 21, 2009 1:31 PM

Uri Dimant said:

Hi Linchi

It is not as I would want  but ate least something:-)

CREATE TABLE author(id_author INT,surname VARCHAR(50))

CREATE TABLE author1(id_author INT,surname VARCHAR(50))

INSERT INTO author1(id_author,surname)

SELECT id_author,surname

FROM (INSERT INTO author(id_author,surname)

      OUTPUT inserted.id_author,inserted.surname

      VALUES(1,'myAuthor')) AS T;

SELECT * FROM author1

Regarding to EXECUTE , I use the old fashioned OPENQUERY command :-)

September 22, 2009 1:41 AM

Linchi Shea said:


Your exmaple is fine. It would be better if it univeral in that you are allowed to select from any well formed resultset. But that is not really the point of this post because even if you can't select from an INSERT or any other DML, the flow of set-based data is not stopped inside T-SQL. You can channel your data using some other methods inside T-SQL. The restrictions on nested INSERT EXECUTE and the batch termination, on the other hand, are insurmontable inside T-SQL. They cause the flow of set-based data come to violent halt.

September 22, 2009 10:14 PM

Linchi Shea said:

A while back, I ranted that the design and implementation of Transact-SQL should be guided by a prime

December 18, 2009 12:16 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement