SQLServerCentral Editorial

SQL Standard Confusion

and ,

Perhaps Yahweh was a bit harsh in his reaction to seeing the Tower of Babel. He looked with trepidation at the power that a single intelligible language, spoken by all, would give mankind. That tower was, alarmingly, being delivered on-time and to specification. 'Nothing that they propose to do will now be impossible for them. Come, let us go down and confuse their language there', he said and scattered them abroad.

This legend, one of the oldest that survive, will always resonate with developers just as it did for the Sumerians. Computer languages achieve their power by their consistency. SQL, sadly, has had the 'confusion' treatment. There is a reasonably consistent declarative syntax for SQL-92 queries but, beyond that, it is tough writing SQL-based applications that are interoperable.

Instead, you are obliged to stick with a particular brand of Relational Database Management System. You may experiment on the nursery slopes of a standard SQL, but the moment you need even just string concatenation or date handling, you see inconsistencies. OK, SQL is a declarative language, not a procedural language, but then even some of the declarative syntax is different. You'll even find implementations that are case-sensitive, completely contrary to the SQL standard. If the Sumerian Lord of Aratta (ca. 21st century BC) were alive today and developing SQL, he'd shake his head sadly and sagely wish "the whole universe, the well-guarded people—may they all query databases together in a single language."

There are several matters standing in the way of any move towards a shared syntax. One problem is the complexity of the SQL standard, now with JSON, time-intervals, pattern-matching and multi-dimensional arrays. No vendor is likely to add them all into their next release, especially if it requires a breaking change for existing users. Another is the fact that the Standards people were very late in deciding that SQL needed procedural extensions, and by then there were several incompatible and competing standards for procedural code for such database objects as functions and batches. This has resulted in database developers being faced with T-SQL, PL-SQL, PSQL, SQL-PSM, PL/pgSQL, SPL and ABAP, all of which established themselves before the SQL standard appeared. To make matters worse, the SQL standard tied down the syntax firmly without so much emphasis on the semantics. We can parse stuff without being clear what it means, and there are implementation aspects such as indexes which are very specific to a particular implementation.

You can't just delegate processes to middleware. The problem is that some processes, such as those within table-valued functions, can provide table-sources, so they are intrinsic to the data layer. The attempt, by ODBC, to provide a consistent syntax for every data source was noble, and worked remarkably well for simple usage, but has had to implement just a very simple subset of SQL. There are no obvious solutions to this problem; we remain boxed into whatever RDBMS we grew up with, suffering acute vendor lock-in.

Phil Factor

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating