Database Consulting Group LLC Blog

At least one ideal to which a database language should strive is the ability to express complex logic simply, concisely, and above all, expressively. This means that having to invoke imperative statements to solve a particular problem should be a last resort within the language. And I suppose that was the case in the story I am about to relate, but the point is, it shouldn't have been!

The problem at hand is a classic hierarchical query with a twist: the tree structure is expressed in a partially temporalized manner that allows for historical as well as future-dated information. Without being able to get into the details, I'll just say that the query to get the actual information as of a given date requires the use of an aggregate. And this leads me to the first problem I had: bit values cannot be used in aggregates. Because no-one would ever want to take the Max() of a bit value. Right.

 Well it just so happens that I did need to take the Max() of a bit, and I assumed that would just work when I was writing the stored procedure embedded in an XML document produced by a C# class generated by a CodeSmith template, but I digress... The point is, it takes about 20 minutes to generate all the classes that actually write the SQL and then rebuild the database with the newly generated SQL procedures. Only then do I actually get the error that Max() cannot be invoked on a column of type bit. So what's the work around:

Max(case when BitColumn = 0 then 0 else 1 end)

Aarrgghhh... But if there's one thing I've learned about T-SQL, it's that the only thing that is consistent about the language is that it is inconsistent. Moving on to the recursive bit of the problem, now that I'm able to express (albeit with some circumlocution) the necessary core part of the query, I decide to use the fancy new common table expression feature of Microsoft SQL Server 2005 to provide a recursive solution to the hierarchical query problem. Beautiful! Or so I hoped...

After quite some time digging through the documentation on these things, I finally figure out how to actually express the solution to my particular problem as a recursive query using a common table expression (the syntax is almost impenetrable, but again, I digress...) and now the compiler blissfully informs me that, wait for it:

'agreggate invocation is not allowed within a recursive query definition'!

Are you kidding me! So once again, a sparkly new feature of SQL Server is hamstrung by an artificial restriction. Hey, at least the product is consistent. Every time I try to use a new feature, I find out that there is some restriction on its usage that renders the feature useless for any real-world scenario.

Take cascading referential integrity, for example. Yeah! We finally have cascading referential integrity! No wait, you can't use it to maintain a hierarchy, that would be far too useful!

Take instead of triggers. Yay! We finally have instead of triggers! No wait, you can't use them to distribute an update to a view that also has an instead of trigger defined, that would be far too useful!

 Take user-defined aggregate functions. Yay! We finally have user-defined aggregate functions! No wait, you can't define them in T-SQL, you have to write a .NET assembly to implement them!

Take disabling of foreign key constraints. Yay! We can disable foreign key constraints instead of having to drop and re-create them! No wait, you still can't truncate a table with foreign key constraints defined, even if they are disabled! That would be far too useful.

Take table-valued functions. Yay! We finally have table-valued functions! No wait, you can't have a table-valued function that takes a table-valued argument! That would be far too useful.

And this list is by no means exhaustive, I could go on, but I seriously digress. The point of all this is that to solve the problem at hand, I was forced to use a cursor. Not because of some theoretical limitation of relational algebra, but because of an implementation detail. Now that's frustrating.

I have encountered problems that I have been unable to express as a cursor, usually things involving inherent ordering or side-effects, but this was not one of them. I should have been able to, and I couldn't, end of. My database language should help me solve problems, not create problems of its own. And that's why we need a new database language. Enter Xix...

Bryn Rhodes
Database Consulting Group LLC