Portable SQL


Portable SQL

Portability is a very desirable feature in any application which stores information in a database. A software developer would like to use a small in-memory database while developing an application, such as Apache Derby, but deploy the finished application to an Oracle database to make use of it’s scalability and high availability features. In order to do this, both database systems need a certain amount of commonality which the application can use.

This commonality was first standardised by ANSI SQL-86. This was the first attempt at standardising SQL, and effectively became a standard which documented the common ground amongst database vendors at the time. One of the major criticisms of SQL-86 was that several important features of relational database systems were not included in this standard.

The US government realised the importance of portability and standardisation, so the Federal Information Processing Standards (FIPS) body mandated standards that must be adhered to in order for technology to be bought by the US government and its agencies. FIPS 127-2 was the database standard mandated by the US government, which was effectively ANSI X3.135-1992. As a result of this, many database vendors adhered to the ANSI SQL-92 standard.

Despite this ideal situation, SQL portability remains elusive. Several explanations exist for why this is so:

  • The standard does not specify database behaviour in several important areas (e.g. indexes), leaving it up to implementations of the database to decide how to behave.
  • The SQL standard precisely specifies the syntax that a conforming database system must implement. However, the standard’s specification of the semantics of language constructs is less well-defined, leading to areas of ambiguity.
  • The complexity and size of the SQL standard means that most databases do not implement the entire standard.
  • The database standard does not prohibit a vendor from providing custom extensions.

The best that can be said of SQL standardisation efforts so far, is that they have been mediocre. ANSI and other standardisation bodies have been playing catchup with database vendors, resulting in specifications which specify the common ground between vendors rather than mandating the important aspects of database operation in a complete and detailed way. The result of which is a standard which has been too easily abused by database vendors.

As an example, limiting the results of a select query to the top 10 result requires various syntaxes on various database implementation as in this table.

Vendor Statement
ANSI select top 10 from table;
DB2 select * from table fetch first 10 rows only;
Informix select first 10 * from table;
Microsoft SQL Server and Microsoft Access select top 10 * from table;
MySQL and PostgreSQL select * from table limit 10;
Oracle select * from (select * from table) where rownum <= 10;

It is reasonable to expect that database vendors would continually outpace any standardisation efforts by offering their own custom extensions. No standardisation effort no matter how good can overcome this, perhaps until everything ever needed is core functionality of the standard.

If an application uses custom, or vendor specific capabilities of a database, then you cannot expect that application to be portable across vendors. Unfortunately, the sad fact of the poor standardisation of SQL means that even writing standard compliant SQL statements, portability across vendors is not assured.

References

FIPS PUB 127-2, Federal Information Processing Standard, 1993. Retrieved 5th June, 2007 from http://www.itl.nist.gov/fipspubs/fip127-2.htm.
Portable SQL, DevPit Contributors, 2007. Retrieved 5th June, 2007 from http://devpit.org/wiki/Portable_SQL.
Structured Query Language, Wikipedia Contributors, 2007. Retrieved 5th June, 2007 from http://en.wikipedia.org/wiki/Sql.

AddThis Social Bookmark Button

Advertisements

One response to “Portable SQL”

  1. Tony Rogerson says :

    This FIPS 127-2 requirement was withdrawn on 5th Feb 08
    (http://www.itl.nist.gov/fipspubs/withdraw.htm). Even the US government now recognises portability is an unachieveable concept in the business and civil environments.

    I feel that the standards people just haven’t kept pace with what people want in the real world to solve their real business problems which is why there are so many custom extensions to each vendors products.

%d bloggers like this: