The Transact-SQL Prime Directive – a bad example

Linchi Shea — Fri, 18 Dec 2009 17:12:00 GMT

A while back, I ranted that the design and implementation of Transact-SQL should be guided by a prime directive that guarantees no interference with the flow of set-based data in Transact-SQL.

That was primarily motivated by the fact that no such guarantee exists today in T-SQL. That is, when you move set-based data around in T-SQL, you may find it stopped dead in tracks for no apparent or meaningful reason.

Recently, I’ve run into another example of not being able to move set-based data around in T-SQL, and here is how to reproduce it.

You need to have three SQL Server 2008 instances, and let’s assume they are SQLA, SQLB, and SQLC.

On SQLB, create a linked server to SQLC, and create the following stored procedure:

use master
go
create proc p_testB
as
create table #tmp(i int)
insert #tmp
EXEC SQLC.master.dbo.p_testC
select * from #tmp
go

On SQLC, create a linked server to SQLB, and create another stored procedure:

use master
go
create proc p_testC
as
create table #tmp(i int);
insert #tmp
select *
from openquery(SQLB, 'select top 1 id from sysobjects')
select * from #tmp
go

Now, if you want to access the resultset of p_testB from SQLA, you naturally expect to be able to do a remote proc call on SQLA as follows (assuming that linked server to SQLB is already created):

EXEC SQLB.master.dbo.p_testB;

The only problem is that the above call does not work, and you would get the following error message:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
Msg 1206, Level 18, State 199, Procedure p_testB, Line 4
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the
distributed transaction.

Now, strictly speaking and to some extent, this is not a T-SQL issue per se. Rather, it has something to do with how SQL Server 2008 handles loopback calls and the implementation of MSDTC.

However, the point is that it does not matter what the underlying root cause for this problem is. What matters is that you have no guarantee that you can move set-based data around in T-SQL, and that is a fundamental problem. If the design and implementation of T-SQL had been guided by a prime directive such as stated earlier, guaranteeing the flow of set-based data would have taken precedence and whatever the underlying limitations with MSDTC (or changes with how loopback calls are handled or problems with any underlying supporting technologies) may be, they would have been fixed/modified/replaced in order to guarantee the set-based data flow.

The Transact-SQL Prime Directive

Linchi Shea — Mon, 21 Sep 2009 12:47:00 GMT

“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 (EXECUTE …)

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?

Search results matching tags 'T-SQL' and 'General trends'

The Transact-SQL Prime Directive – a bad example

The Transact-SQL Prime Directive