Feeds

First among SQLs

COBOL for lawyers

  • alert
  • submit to reddit

Maximizing your infrastructure through virtualization

Stob Legend has it that Edgar 'Ted' Codd got the idea for SQL while attending a 'Sky at Night' spin-off lecture. Patrick Moore, pointing at the blackboard, said: 'Select a star from the table'. 'That's it!', cried Codd, and ran out the door to follow up his inspiration forthwith, missing a good discussion of vulcanoids.

Though born in the '70s, SQL is sentimentally the last of the 1950s languages. While kernighan & pike & ritchie & thompson were going all lower case & curly brackets, the relational pioneers wanted S.Q.L.'s natural temporal home to be with F.O.R.T.R.A.N. and A.L.G.O.L and line printers the size of an Austin Mini. To this day, proper SQL is still mostly written in CAPS - ask any Oracle.

In the same way that COBOL was designed so that businessmen could use it, SQL was similarly imagined so that lawyers could easily pick it up. If you look carefully, you can still detect hints of this legal element in modern SQL code. Its best syntax has that quality of redundant thoroughness that one associates with the small print in licence agreements. A well-drafted SQL statement should look like shouting, but shouting in a nerdy-wordy way. Think John Major defending the Maastricht treaty at the dispatch box:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

GRANT SELECT, INSERT, UPDATE ON suppliers TO my_right_honourable_friend

Nonetheless some of the function names do have a certain kind of strange beauty, which the strstr() vowel-evacuated standard C library rather lacks. For example

COALESCE(ColumnName, ReplacementValue)

sounds like something that happens to null values if they are left out too long in the sun. And so it proves.

The early years of SQL were unhappily mostly taken up with fierce, unproductive disputes regarding which level of normal form was appropriate for practical database design. There was a period when admitting that one only designed one's tables to anything less than the ultra-strict Seventh Normal Form ('values of attributes in a string column not allowed to rhyme, grasshopper') was a social faux pas equivalent to being caught pinching the Pick 'n' Mix in Woolworths.

I will draw a veil over those unhappy times, and also over some of the escapades of the rather excitable denormalisation movement, which famously climaxed in the so-called 'Six O'Clock News incident', with one of their members humiliatingly being sat on by Nicholas Witchell.

SQL, the language

SQL is not without visionary simplifications of the kind that underlie all great programming languages. Whereas lesser parsers report a range of errors in the code fed to them, a strictly standard SQL may only report one:

error near ","

which was cunningly devised to be true-ish under nearly all circumstances.

(Most real-life RDBMSes do not adhere to this rule, but nonetheless stick to the spirit of error concealment. A colleague thoughtlessly named a column Address Id (sic), and in due course received the brusque and baffling warning

JOIN operator not supported

whereas what was actually meant was, of course,

Don't put spaces in column names you dork

He complained bitterly about this, but I cannot agree. Twenty-first century programmers are already ridiculously feather-bedded. Mutter mutter grump.)

Those of you familiar with modern exception-handling will now be wondering if SQL has an EXCEPT keyword. Certainly it does. However, there are two minor problems:

  1. The version of SQL you are using doesn't implement it
  2. It has nothing to do with exceptions.

Devised as part of SQL's cap-doffing to set theory, and of course the late Dr John Venn and his marvellous diagrams, EXCEPT is a sibling operator to the more familiar UNION; the former differing from the latter only in the detail that pretty well nobody knows what it means, and those who do know what it means don't use it.

In fact, great swathes of the SQL standard are unimplemented in actual RDBMSs. SQL standards define features that aren't implemented in the same way that Lieutenant Scheisskopf cancelled non-existent parades in Catch-22. So much lives below the water line of implementation, the language is an ISO iceberg. For example:

  • CHECK constraints - you were supposed to be able to include SELECT statements in them. Bet your database software doesn't allow this.
  • MERGE - combines UPDATE and INSERT, in the same way truth and justice may be combined to produce the superior concept of  'trustice'.
  • Collection types - declare your columns to be arrays and multisets, a nod to the objects craze of the early 1990s.
  • CUBE and ROLLUP - a nod to the data warehousing craze of the late 1990s.
  • A formidable wodge of XML stuff you wouldn't touch with a proverbial - a nod to the XML craze of the early 2000s
  • WIDTH_BUCKET - no idea what it is supposed to do; just liked the name.

This is but a tiny proportion of what your relational database probably doesn't offer you. Cheer up. At least this way you don't waste time thinking of ways to use this stuff.

The Power of One Infographic

More from The Register

next story
NO MORE ALL CAPS and other pleasures of Visual Studio 14
Unpicking a packed preview that breaks down ASP.NET
Cheer up, Nokia fans. It can start making mobes again in 18 months
The real winner of the Nokia sale is *drumroll* ... Nokia
Mozilla fixes CRITICAL security holes in Firefox, urges v31 upgrade
Misc memory hazards 'could be exploited' - and guess what, one's a Javascript vuln
Put down that Oracle database patch: It could cost $23,000 per CPU
On-by-default INMEMORY tech a boon for developers ... as long as they can afford it
Google shows off new Chrome OS look
Athena springs full-grown from Chromium project's head
Apple: We'll unleash OS X Yosemite beta on the MASSES on 24 July
Starting today, regular fanbois will be guinea pigs, it tells Reg
HIDDEN packet sniffer spy tech in MILLIONS of iPhones, iPads – expert
Don't panic though – Apple's backdoor is not wide open to all, guru tells us
prev story

Whitepapers

Designing a Defense for Mobile Applications
Learn about the various considerations for defending mobile applications - from the application architecture itself to the myriad testing technologies.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Top 8 considerations to enable and simplify mobility
In this whitepaper learn how to successfully add mobile capabilities simply and cost effectively.
Seven Steps to Software Security
Seven practical steps you can begin to take today to secure your applications and prevent the damages a successful cyber-attack can cause.
Boost IT visibility and business value
How building a great service catalog relieves pressure points and demonstrates the value of IT service management.