SQL

Mark Whitehorn takes a look at Structured Query Language.

HardCopy Issue: 39 | Found In: Development | Published: 01/02/2008 | Last Revision: 06/07/2010

Structured Query Language works with relational database engines such as SQL Server or Oracle and, as the name suggests, is designed for querying databases. This is performed with SQL’s DQL (Data Querying Language) component. However SQL can also be used to create and manipulate databases using its Data Definition Language (DDL) and Data Manipulation Language (DML) components. The formal specification of SQL completely omits any flow control elements and user interface building capabilities (although some manufacturers have produced variants that make use of extensions to the language in order to include some of these). To anyone familiar with Visual Basic, Java or the like, this must appear a weird omission but there’s method in this apparent madness. From the outset SQL was designed specifically to perform ‘set-based operations’ very efficiently, which effectively means it’s optimised to work with tables of data. Imagine querying a table with a million rows. Using a procedural language like C# you would have to write code to ensure that an operation was performed on every row using flow control commands such as a For..Next loop. However SQL is built from the ground up to assume that any querying operation will be performed against every row in the table. So you simply declare the operation you wish carried out and SQL automatically applies it to every row in the table – the need for flow control disappears. This makes SQL very focused and, like any language, it’s incredibly efficient once you know it – particularly as you can embed SQL code into other languages. You can use SQL to perform the set manipulations and, if you require UI or flow control, a language that has these components for the rest. SQL is not competing with other languages, it is complimenting them and extending their adaptability. SQL came into being at IBM in the early 1970s and in 1999 the SQL-99 standard (sometimes known as SQL3) was laid down. Additions to the standard in 2003 and 2006 have added XML-related features. Adoption of change is slow in the SQL world which means SQL-99 remains the version most frequently and fully supported. SQL has become the de facto language for database engines such as SQL Server, Oracle, DB2, MySQL and SQLite. The good news is that the core language remains the same for each engine, the bad is that the precise language used for each engine has dialectic differences. So, if you are developing on one platform but intend to port to another, it is worth sticking to just the core SQL.