Tag Archives: SQL

You were never meant to do that with SQL

There seems to be a lot of hatred for SQL in the world at the moment: I can’t think of any other reason why the term NoSQL would catch on in the way that it has, when the key technological distinction is actually the lack of ACID guarantees (which are entirely orthogonal to whether or not SQL is used, as evidenced by non-ACID MySQL and HiveQL, which offers a pretty familiar SQL-like interface on an entirely non-traditional backend).

I wonder whether one of the unspoken reasons for this hatred is that at one point or another almost everyone has ended up doing this sort of thing:

   builder.Add("SELECT foo FROM bar WHERE id = ");

   if (additionalConstraint)
      builder.Add(" AND frobbable = 1 ");

   /* ... ad nauseam ... */

SQL is a hard language to like: it’s never been properly standardised (or rather, it has, but the standard has never been implemented) meaning that you spend too much time worrying about compatibility. Its theoretical underpinning is poor, leading to constructions that are hard for the engine to optimise (meaning more manual work).

However, SQL is a language in its own right, and was never intended to be generated programmatically by another programming language. This shouldn’t come as a surprise, as I struggle to think of any programming language that has been designed to work in this way.

Using SQL from a decent command-line environment is a powerful tool and often a pleasure to use. By comparison, generating SQL programmatically is an abomination that would be worth of The Daily WTF were it not for the fact that nobody’s ever invented an API that offers the same flexibility.

Personally, I blame the vendors. Until RDBMSs can offer the same quality of optimisation that modern compilers can (that is, write in a high-level language and never even think about micro-optimisation) high-performance relational database access will remain a sea of vendor-specific optimiser hacks. Maybe there’s a theoretical reason why optimisers will never be this good, in which case perhaps we do need to abandon the relational model in practice. But let’s not pretend it has anything to do with SQL.

Book Review: SQL and Relational Theory

Front cover of the book "SQL and Relational Theory"

The first thing to know about SQL and Relational Theory is that it’s largely a retread of Chris Date’s previous excellent book Database in Depth. The latter is a favourite of mine: extremely readable, yet with enough theoretical clout to change the way I looked at databases forever. The new volume carries over large chunks of the text from the older one, with some minor tweaks. As the name suggests, it brings in substantial additional material to link relational theory in with SQL, the only practical implementation of the model in current use.

The front cover of "Database in Depth"

In the preface, Date explains that the motivation for the new book was the realisation that practitioners weren’t able to figure out for themselves how to apply his theoretical ideas within SQL. Clearing up this difficulty is an admirable goal, and illustrates well that Date’s approach is practical and not meant as ivory-tower theory, but I can’t help but wonder if one of the reasons he didn’t state was that books sell better with ‘SQL’ in the title.

The additional material has resulted in a book that is roughly twice as long. This isn’t a problem in itself, though it does spoil one of the things I loved about “In Depth”: that it could be read in a couple of evening’s work by a sufficiently motivated person. The importance of making a book light enough that you can sit and read it on the sofa without looking like a database nerd should not be understated.

The prose remains clear and readable, and strikes a nice balance that makes it approachable to relative beginners while avoiding ever sounding patronising. Date’s style is precise to a fault, and some people will find it needlessly pedantic; nevertheless, there isn’t any pointless pedantry here, and if you stick with it you’ll learn why subtle distinctions need to be made.

So how useful are the new insertions on SQL? I find it difficult to tell. On the one hand, it makes it much easier to relate the ideas in this book to discussions of theory that actually occur in the real world, since SQL is the lingua franca. In the old book, it was certainly annoying to have all the examples written in Tutorial D, without a real specification of how the language works. On the other hand, Date’s examples in this book are still in a mythical beast called “Standard SQL”, of which no practical implementation exists. What is good practice in standard SQL might be impossible in your chosen implementation, or there might be a better way to achieve the same thing.

It’s certainly worth buying one of the two books here, but the choice of which is not as obvious. If you already own “In Depth”, the updated version probably isn’t worth buying. If you don’t, then “SQL and Relational Theory” is the thing to buy, unless you’re after a lighter and more portable read.