In this article, Vikram Vaswani discusses the ways in which SQL plays an important role in the computer market today, and what may be in store for this database language in the future. This excerpt comes from chapter 26 of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
The adoption of an official ANSI/ISO SQL standard was one of the major factors that secured SQL’s place as the standard relational database language in the 1980s. Compliance with the ANSI/ISO standard has become a checkoff item for evaluating DBMS products, so each DBMS vendor claims that its product is compatible with or based on the ANSI/ISO standard. Through the late 1980s and early 1990s, all of the popular DBMS products evolved to conform to the parts of the standard that represented common usage. Other parts, such as the module language, were effectively ignored. This produced slow convergence around a core SQL language in popular DBMS products.
As discussed in Chapter 3, the SQL1 standard was relatively weak, with many omissions and areas that are left as implementation choices. For several years, the standards committee worked on an expanded SQL2 standard that remedies these weaknesses and significantly extends the SQL language. Unlike the first SQL standard, which specified features that were already available in most SQL products, the SQL2 standard, when it was published in 1992, was an attempt to lead rather than follow the market. It specified features and functions that were not yet widely implemented in current DBMS products, such as scroll cursors, standardized system catalogs, much broader use of subqueries, and a new error message scheme. DBMS vendors are still in the process of evolving their products to support the full features of SQL2. In practice, proprietary extensions (such as enhanced support for multimedia data or stored procedures or object extensions) have often been more important to a DBMS vendor’s success than higher levels of SQL2 compliance.
The progress of the SQL standards groups continued, with work on a SQL3 standard begun even before the SQL2 standard was published. As delays set in and the number of different areas to be addressed by the next standard grew, the work on SQL3 was divided into separate, parallel efforts, focused on the core of the language, a Call-Level Interface (CLI), persistent stored modules (stored procedures), distributed transaction capabilities, time-based data, and so fourth. Some of these efforts were published a few years later as enhancements to the 1992 SQL2 standard. A SQL2-compatible CLI standard was released in 1995, as SQL-CLI. A year later, in 1996, a standardized stored procedure capability was released as SQL-PLM. In 1998, object language bindings for SQL were standardized in the SQL-OLB specification. A basic set of OLAP capabilities were published in a SQL-OLAP standard in 2000.
While progress continued on these additions to the SQL2 standard, the work on the core language part of SQL3 (called the foundation part of the standard) focused on how to add object capabilities to SQL2. This quickly became a very controversial activity. Relational database theorists and purists took a strong stand against many of the proposed extensions. They claimed that the proposals confuse conceptual and architectural issues (e.g., adding substructure beyond the row/column tables) with implementation issues (e.g., performance issues of normalized databases and multitable joins). Proponents of the proposed SQL3 object extensions pointed to the popularity of object-oriented programming and development techniques, and insist that the rigid row/column structure of relational databases must be extended to embrace object concepts or it would be bypassed by the object revolution. Their argument was bolstered in the marketplace as the major relational DBMS vendors added object-oriented extensions to their products, to blunt the offensive from pure object-oriented databases, and were largely successful with this strategy.
The controversy over the SQL3 work was finally resolved after a seven-year effort, with the publication of the SQL:1999 standard. (The term SQL3, which was used during the development of the standard, has now been replaced by the official term SQL:1999.) The SQL:1999 standard is structured in a series of parts:
Part 1: Framework. Describes the overall goals and structure of the standard, and the organization of its other parts.
Part 2: Foundation. Is the main body of the standard, focused on the SQL language itself. SQL statements and clauses, transaction semantics, database structure, privileges, and similar capabilities are specified here. This part also contains the object-oriented extensions to SQL.
Part 3: Call-Level Interface. Contains the SQL-CLI (1995) extensions to the SQL-92 standard, updated to conform to SQL:1999.
Part 4: Persistent Stored Modules. Similarly contains the SQL-PSM (1996) extensions to the SQL-92 standard, updated to conform to SQL:1999.
Part 5: Host Language Bindings. Deals with the interactions between procedural host languages (such as C or COBOL) and SQL.
Part 9: Management of External Data. Describes how a SQL-based database should manage data external to the database itself.
Part 10: Object Language Bindings. Deals with the same issues as Part 5, but for object-oriented languages.
Some parts of the standard are still under development at this writing, as indicated by the missing part numbers. In addition, other SQL-related standardization efforts have broken off into their own, separate standards activities. A separate standard is under development for SQL-based handling of multimedia data, such as full-text documents, audio, and video content. This is itself a multipart standard; some parts have already been published. Another separate standard makes official the embedded SQL for Java work known as SQLJ.
In the progression from SQL1 to SQL2, and then to SQL:1999, the official ANSI/ISO SQL standards have ballooned in scope. The original SQL1 standard was less than 100 pages; the Framework section (Part 1) of the SQL:1999 standard alone is nearly that large. The Foundation section of the SQL:1999 standard runs well over 1000 pages, and the currently published parts, taken together, run over 2000 pages. The broadly expanded scope of the SQL:1999 standard reflects the wide usefulness and applicability of SQL, but the challenge of implementing and conforming to such a voluminous set of standards is very formidable, even for large DBMS vendors with large development staffs.
It’s worth noting that the SQL:1999 standard takes a very different approach to standards conformance claims than the SQL1 and SQL2 standards. The SQL2 standard defined three levels of conformance, Entry, Intermediate, and Full, and laid out the specific features of the standard that must be implemented to claim conformance at each level. In practice, DBMS vendors found some features at each level to be important to their customers, and others relatively unimportant. So virtually all current SQL implementations claim some form of compliance with SQL2, but very few, if any, implement all of the features required for formal Intermediate or Full conformance.
With this experience in mind, the SQL:1999 standards group instead defined only one Core SQL level of conformance, which corresponds roughly to the Entry level of SQL2 plus selected features from the Intermediate and Full levels. Beyond this Core SQL, additional features are grouped together in packages, to which conformance can individually be claimed. There is a package for the SQL-CLI capabilities, one for SQLPSM, one for enhanced data integrity functions, one for enhanced date and time functions, and so on. This structure allows individual DBMS vendors to pick and choose the areas of the standard that are most important to the particular markets they serve, and makes conformance to parts of the standard more practical.
At this writing, the SQL:1999 standard is too new to fully gauge its impact on the DBMS market. If the experience with SQL2 is any guide, vendors will carefully evaluate individual new pieces of SQL:1999 functionality and seek feedback from their customer base about which ones are useful. With the very large new functionality required by SQL:1999 features such as user-defined types and recursive queries, implementation of some parts of SQL:1999 will be a multiyear project for even the largest DBMS vendors. In practice, the SQL1 (SQL-89) standard defines the core SQL capabilities supported by virtually all products; the SQL2 (SQL-92) standard represents the current state of the art in large enterprise database products, and the SQL:1999 standard is a roadmap for future development.
In addition to the official SQL standard, IBM’s and Oracle’s SQL products will continue to be a powerful influence on the evolution of SQL. As the developer of SQL and a major influencer of corporate IS management, IBM’s SQL decisions have always had a major impact on other vendors of SQL products. Oracle’s dominant market position has given it similar clout when it has added new SQL features to its products. When the IBM, Oracle, and ANSI SQL dialects have differed in the past, most independent DBMS vendors have chosen to follow the IBM or Oracle standards.
The likely future path of SQL standardization thus appears to be a continuation of the history of the last several years. The core of the SQL language will continue to be highly standard. More features will slowly become a part of the core, and will be defined as add-on packages or new standards in their own right. Database vendors will continue to add new, proprietary features in an ongoing effort to differentiate their products and offer customers a reason to buy.
Remember: this is chapter three of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.