SQL: The Standard and the Language

Jim Melton

The SQL Query Language

SQL is a language designed especially for access to relational database systems; it is arguably the most significant relational database language in existence...although it does not strictly implement the relational model at all! SQL, which was initiated by IBM as part of its System R research prototype in the late 1970s, has been implemented by more than 20 vendors and is available in some form on virtually every important computer platform in the world. SQL's model of data is one of "tables", corresponding to relations in the relational model; this tabular view of data provides columns (having names and data types) and rows (containing the actual data) and a uniquely intuitive way of looking at data. Its most important improvement over earlier data models is that operations on data are specified in non-procedural ways and entire collections ("sets") of data are manipulated with single statements. This notion removed from application writers the burden of "navigating" around a complex database structure and significantly reduced the amount of code that had to be written for any specific purpose.

In 1986, the American National Standards Institute (ANSI) published the first de jure SQL standard, X3.135-1986; the International Organization for Standardization (ISO) published a technically-identical standard, ISO 9075-1987, a few months later in early 1987. That standard was widely viewed as a "least common denominator" of existing SQL implementations and consequently failed to standardize many popular and necessary features of the language. This standard provided the ability to invoke SQL capabilities from four programming languages: COBOL, FORTRAN, Pascal, and PL/I.

In 1989, both ANSI and ISO published replacement standards (ANSI X3.135-1989 and ISO/IEC 9075:1989) that slightly enhanced the language and also added an optional capability called referential integrity, which allowed database designers to require certain relationships between data in different parts of the database. In the same year, ANSI published X3.135-1989 to add support for two additional programming languages, Ada and C.

In this general timeframe, X/Open published its first specification of SQL, not directly tied to the ANSI or ISO standards, but specifically designed to reflect the existing products provided by X/Open shareholders to their customers. For several years, X/Open's Portability Guides kept this "what's available" approach, but it became apparent that vendors were using the absence of X/Open requirements as a reason for not implementing features required by the de jure standards: the de facto standard began to inhibit technological advances. As a result of this observation---coupled with X/Open's adoption of a strategy of alignment with de jure standards wherever applicable---the SQL specification began to evolve to a "programmer's guide" based very closely on SQL-89. The value added by X/Open's document included a much more thorough specification of "limits", such as the minimum number of tables that an implementation must support, a precise selection of precisions for numeric values and lengths for character strings, and so forth. X/Open SQL continued to contain certain important (and widely implemented) features, like indexes, that the standard does not address.

Levels of SQL

In 1992, when ANSI and ISO published the third revision of the SQL standard (X3.135-1992 and ISO/IEC 9075:1992), X/Open quickly moved to align with the lowest level, Entry SQL, of that standard, while taking important features from Intermediate SQL as well (notable dynamic SQL, the remote connection capability, and certain diagnostics capabilities). In the United States, NIST (the National Institute of Standards and Technology) had developed a conformance testing suite for SQL-89 whose requirements were reflected in a Federal Information Processing Standard (FIPS 127-1); the publication of SQL-92 was accompanied by a revised FIPS 127-2 and an updated test suite. Both FIPS 127-2 and the test suite reflect NIST's belief that the step from Entry SQL to the next level in the standard, Intermediate SQL, was too large for vendors to accomplish in a year or two; NIST therefore specified a level called Transitional SQL for which conformance could be claimed for a limited time (hence the name "Transitional").

In 1993, after adoption of FIPS 127-2, X/Open began development of the next version of its SQL Common Application Environment specification, designed to align with Transitional SQL. In addition to maintaining the traditional X/Open additions like indexes, this CAE-in-progress is expected to reach into higher levels of SQL-92 and even the unfinished next generation of the language, "SQL3". One especially important feature currently being adapted by X/Open is the "stored routine" capability in development for the SQL standard; using stored routines, an application can gain tremendous performance advantages and even move entire portions of application code into the database server.

Testing and branding

X/Open has a long tradition of branding products that are based on their specifications; SQL products are no exception to this practice. However, in the past, X/Open has not developed a branding suite for SQL, so vendors were forced into doing their own best-effort testing and claiming conformance to get the X/Open brand. That brought with it a contractual requirement to correct any conformance deficiencies within a specified period following their reporting by any interested party. The inconveniences---to users and to vendors---of this approach made the existence of a testing capability very important. Even though NIST has a conformance test suite for SQL implementations, it is closely tied to U.S. Federal Government requirements and is not always comfortably viewed by users in other nations. More important, the NIST suite doesn't test the X/Open features like indexes, so neither vendors nor users were protected in such areas by NIST's testing. To resolve this problem, X/Open has entered into an agreement with both NIST and with the National Computing Centre (NCC) in the UK to develop a more comprehensive test suite, with NIST providing a Transitional SQL test capability, X/Open providing additional tests for X/Open-only features, and NCC adding tests to cover Intermediate SQL. Appropriate licensing agreements will make it possible for independent testing laboratories to test SQL products, issue conformance certificates and reports that will X/Open to provide product brands. All this will be backed up by legal obligations, including a Conformance Statement Questionnaire that every vendor must complete for a product to be branded, identifying all options, limits, and so on that the product provides.

Benefits of X/Open SQL

All this sounds like a lot of trouble, doesn't it? Why would a vendor want to go to all this pain just to have the privilege of putting the X/Open logo onto his products and advertising? Indeed, why would a user out shopping for an SQL product bother to look for the X/Open logo?

It all boils down to the voluntary nature of standards. Standards provide a (usually!) well-defined way to do things, but they can't force anybody to do it that way. However, the computer industry's focus on "openness" has led both vendors and purchasers to look for ways to reduce costs (development costs and acquisition/use costs, respectively) while increasing their options (marketplaces and sources, respectively). Standards---both de jure and de facto---provide a path towards this goal. But that path has proved to be remarkably rocky and ill-defined.

It is through the existence fo branding and certification facilities like X/Open's that users can cut through the metaphorical underbrush and locate products---without going to enormous trouble---that implement the capabilities they need...and that they have specified. If users know that, by looking for the X/Open brand, they are specifying and then purchasing a product with known capabilities that will run their applications, then they can make purchases with less effort and more quickly. And they run a far lower risk of being disappointed.

Similarly, vendors can know that their display of X/Open's brand guarantees them a reasonable opportunity to sell their products to a well-known market...customers who are aware of the brand. These vendors don't have to worry about "what set of features should we implement for market x" and can focus on "how can we implement that required set of features most efficiently" or "how can we get the product to market most quickly". In short, everybody benefits (except, perhaps, those most interested in the proprietary world!). Particularly with X/Open's increased thrust towards state-of-the-art technology instead of least-common-denominator specs, vendors can find themselves challenged to keep up instead of worrying about inventing new features that might---or might not---be interesting to broad categories of users.

What will the future hold?

Nothing stands still, least of all technology. (Even mountains move: the early-1994 earthquake in Southern California resulted in at least one 1000+ meter peak being raised by over 40 cm and moving to the northwest by about 6 cm!)

The SQL standard continues to evolve, with another replacement expected to be published in 1996 or 1997 (my money's on the latter). X/Open is actively participating in the development of SQL3, partly by early adoption of the stored routine capability mentioned above and giving feedback to ANSI and ISO during this work.

Undoubtedly, the most dramatic development in SQL since its inception is SQL's addition of object-oriented capabilities to the language. While this is extremely controversial and, like much of the object-oriented world, there is not yet uniform agreement on exactly what must be done (never mind how), there is unanimous agreement among the SQL vendors that this will be the shape of the future. The SQL standard is also adopting that great missing data type so widely implemented by vendors, the BLOB (Basic Large OBject)...whose name has nothing to do with object orientation (surely this doesn't suggest that we've reached the limits of English expressitivity?). BLOBs are used to capture very large chunks of data that are not (usually) otherwise acted on by the database system---they provide only a storage mechanism. X/Open is poised to adopt this specification early on. Beyond that, X/Open will, as always, study the marketplace, work directly with users, gathering requirements, analyzing them, and helping the formal standards efforts with that knowledge.

Wherever possible we have identified contact points for further information. If you can't find the information you need, please contact X/Open at any of of the following locations:

Copyright X/Open Company Limited, © 1994