SQL – does exactly what it says on the tin

SQL how unloved it must feel sometimes, constantly being maligned, accused of being on the wrong side of the object-relational impedance mismatch,  lacking the glamour of OO programming languages that claim the moral high ground. Yet at the same time hewing and hauling most of the world’s structured data on its old but well fashioned back.

SQL is perhaps the world’s most popular DSL, a declarative language for the manipulation of tabular data, easy to learn yet capable of powerful (and sometimes complex) expressions.  And like the Ronseal ad, a SQL statement no matter how simple or complex, does exactly what it says, all the complexity of loops and iterations and the attendant errors, abstracted away, it just works!

SQL is both a programmer and an end-user tool; after Excel formulas, it’s the language most likely to be understood and used by “civilians”.  There are few enough such cross-over tools, so think twice before building a datastore that doesn’t offer a SQL API.  And I guess that’s what Amazon did. Although SimpleDB is not a relational database, they’ve decided to add a SQL API, following Google’s lead with its SQL front-end to the non relational big-table backed Google App datastore.

SQL is also the reason why I’ve integrated SQLite with Excel , leveraging SQL to manipulate tabular data with greater efficiency and fewer errors while still keeping the touchy-feely power of Excel.   I expose SQLite to Excel via UDFs rather than menu options or wizards, so that the transformation logic is visible and approachable (at least to those comfortable with excel formula “programming” and with basic SQL).

SQL is my weapon of choice because of my belief in the primacy of data. It is data that matters in the long run, not the algorithms or GUIs that temporarily use (and abuse) it.  In my time in Guinness Ireland I had the task of transferring master and historical transactional data from “legacy systems” into SAP ,Siebel and a new datawarehouse; data that had a decade and a half earlier been transferred by me  into those same legacy systems from even older systems. In fact, the data’s electronic lineage could be traced back to a 1960’s era ICL mainframe  (I have the original spec!) and I’m sure it existed in accountancy machine punch-cards  prior to that. Understand a business’s data and you’ll not just understand the business as it currently operates but also how it operated in the past and its future potential.

SQL abú.

Why not join me on Twitter at gobansaor?

Advertisements

14 responses to “SQL – does exactly what it says on the tin

  1. Tom,

    Great Post. I just love this parting shot

    >>>>>>>>>>>>>>
    Understand a business’s data and you’ll not just understand the business as it currently operates but also how it operated in the past and its future potential
    <<<<<<<<<<<<<<

    As you know, I’m an accounant and “part” techie, so it chimes – data is my stock in trade.

    But.. Clive (@positivechurn) is a sales guy – and his first port of call is always the data.

    Which is why we get on so well.

    And why Severn Delta is so well run (even if I do say so myself).

    Martyn

  2. Well said Tom.

    I think SQL may be one of the greatest successes of computing.

    Let’s hope we see many more SQL:2009 specs! 🙂

  3. @Martyn

    I’m a techie and “part” business analyst so data is also my stock in trade. Data lies at the intersection of technology and business fields and is why I chose to style myself a datasmith. Most others I would class as fellow datasmiths tend to come from the business side of the house (like yourself) but many in IT have also seen the light (mainly as a result of the onward march of BI systems).

    Perhaps we need to formalise datasmithing as a subject area common to both IT and Business training. Covering areas such as Spreadsheets, database design, SQL, OLAP, applied statistics, ETL, data quality and governance etc..

    @Nicholas

    Perhaps we should keep SQL’s power a secret, create a inner circle of SQL wizards. One statement to rule them all (crack of thunder, flash of lighting, evil laughter echoes through the land)

    Tom

  4. >>
    Perhaps we need to formalise datasmithing as a subject area common to both IT and Business training. Covering areas such as Spreadsheets, database design, SQL, OLAP, applied statistics, ETL, data quality and governance etc.
    <<

    What a seriously great idea!

    Martyn

  5. The world’s most popular DSL? What about HTML, JavaScript, CSV, Excel formulas, batch files, shell scripts, and the URL? And doesn’t Word have a little DSL for describing page headers and footers? And then there’s bbCode, Markdown, Textile, Wikipedia markup (you can program calendars in that!) and so on. SQL isn’t even close.

  6. Uh, which is not to say SQL is not awesome.

  7. Hi Kragen,

    Yes of course, you’re right (most definitely in the cases of HTML and Excel formulae; CSVs and URLs? why stop there, why not microwave control panels, SKY remote controls,Windows GUI, plain ascii text files; and I think those responsible for EMACScript would object to their language being described as domain specific), but I did say, perhaps, so I can’t be sued 😉

    And popular, well like as in high-school, popularity tends to be a function of the networks you encounter in your daily life. We all have own worlds and mine is that of data as a business resource.

    As for Excel formulae, I’m blinded by the fact that I’m so in awe of the spreadsheet as a general purpose tool, I don’t regard it as ‘domain specific’ but as the hammer to hit everything be it a nail or not!

    And yes you are again right, SQL is awesome.

    Tom

  8. Microwave control panels and TV remote controls are not, in themselves, languages; typically the only grammar they support is place-value in numbers, a “DSL” that goes back thousands of years. CSV and URLs are complex enough that it makes sense to use parsing tools like PEGs or yacc to handle them, and to explain them to people as languages.

    Any Turing-complete DSL, such as ECMAScript/JavaScript, is in some sense general-purpose, and over time it may become general-purpose. REXX and Perl followed that path, and JavaScript seems to be doing it too.

    It’s true that the popularity we each perceive differs from real, objective popularity because of this network thing. That doesn’t mean there is no objective measure of popularity.

    Excel formulæ aren’t even Turing-complete.

  9. @Kragen

    One man’s GPL is another’s DSL and yet another’s API, http://martinfowler.com/bliki/DslBoundary.html

    “Excel formulæ aren’t even Turing-complete” – yet they still can ‘solve’ a huge range of problems, particularly when array formulae, named ranges and data tables are used.

    As for “That doesn’t mean there is no objective measure of popularity.” – well as Fr. Jack Hackett would say “That would be an ecumenical matter!”

    Tom

  10. Pingback: Excel as a document-oriented NoSQL database « Gobán Saor

  11. Pingback: SQLite XML Streaming Virtual Table via Expat « Gobán Saor

  12. Pingback: LightSwitch & Hobo – the return of the 4GL? | Gobán Saor

  13. Pingback: Those with a datasmith’s hammer, see every problem as a table. | Gobán Saor

  14. Pingback: JavaScript as an Excel scripting language via HAMMER | Gobán Saor