|Table of Contents|
1.2 History of the SQL Standard
Because SQL dialects began to proliferate, the ANSI published its first SQL standard in 1986 to bring greater conformity among vendors, and a second, widely-adopted standard in 1989. The International Standards Organization (ISO) also approves the SQL standard. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Now that the ANSI standard has advanced, we generally use the industry standard term SQL2003. Each time it revises the SQL standard, ANSI adds new features and incorporates new commands and capabilities into the language. For example, the SQL99 standard added a group of capabilities that handled object-oriented datatype extensions.
1.2.1 What's New in SQL2003
SQL99 had two main parts, Foundation:1999 and Bindings:1999. The SQL2003 Foundation section includes all of the Foundation and Bindings standards from SQL99, but a new section called Schemata was created.
The Core requirements of SQL2003 did not change from Core SQL99. So the database platforms that conformed to Core SQL99 automatically conform to SQL2003. Although the Core of SQL2003 has no additions (except for a few new reserved words), a number of individual statements and behaviors have been updated or modified. Because these updates are reflected in the individual syntax descriptions of each statement in Chapter 3, we won't spend time on them here.
In addition, a number of features, most of which were rather obscure, have been added, deleted, or renamed. At this point in time, many of the new features of SQL2003 standard are interesting mostly from an academic standpoint because none of the database platforms support the feature yet. However, there are a few new features that hold more than passing interest:
Another important change from SQL92 is that SQL99 is built upon SQL92's levels of conformance.
1.2.2 Levels of Conformance
SQL92 first introduced levels of conformance by defining three categories: Entry, Intermediate, and Full. Vendors had to achieve at least Entry-level conformance to claim ANSI SQL compliance. The U.S. National Institute of Standards and Technology (NIST) later added the Transitional level between the Entry and Intermediate levels. So NIST's levels of conformance were Entry, Transitional, Intermediate, and Full, while ANSI's were only Entry, Intermediate, and Full. Each higher level of the standard was a superset of the subordinate level, meaning that each higher level of the standard included all the features of the lower level of conformance.
Later, SQL99 altered the base levels of conformance. Gone were the Entry, Intermediate, and Full levels of conformance. With SQL99, vendors must implement all the features of the lowest level of conformance, Core SQL99, in order to claim (and publish) that they are SQL99 ready. Core SQL99 includes the old Entry SQL92 feature set, features from other SQL92 levels, and some brand new features. A vendor may also choose to implement additional feature packages described in the SQL99 standard.
1.2.3 Supplemental Features Packages in the SQL2003 Standard
The SQL2003 standard represents the ideal, but very few vendors currently meet or exceed the Core SQL2003 requirements. The Core standard is like the interstate speed limit: some drivers go above, others go below, but few go exactly the speed limit. Similarly, vendor implementations can vary greatly.
The nine supplemental features packages, representing different subsets of commands, are platform-optional. Some features might show up in multiple packages, while others do not appear in any of the packages. These packages and their features are described in the list that follows.
Two committees-one within ANSI, the other within ISO, and both composed of representatives from virtually every RDBMS vendor-drafted the definitions shown in the list. In this collaborative and somewhat political environment, vendors compromised on exactly which proposed features and implementations were incorporated into a new standard.
Many times, a new feature in the ANSI standard is derived from an existing product or is the outgrowth of new research and development in the academic community. Consequently, vendor adoption of specific ANSI standards can be spotty at times. A relatively new addition to the SQL2003 standard is SQL/XML. The other parts of the SQL99 standard are persisted in SQL2003, though their names may have changed or they may have been slightly rearranged.
Note that parts 5, 6, 7, and 8 do not exist by design.
Be aware that an RDBMS platform may claim SQL2003 compliance by meeting Core SQL99 standards, so read the vendor's fine print for a full description of its ANSI conformity features. By understanding what features comprise the nine packages, you gain a clear idea of both the capabilities of a particular RDBMS and how the various features behave when SQL code is transported to other database products.
The ANSI standards-which cover retrieval, manipulation, and management of data in commands, such as SELECT, JOIN, ALTER TABLE, and DROP-formalize many SQL behaviors and syntax structures across a variety of platforms. These standards become even more important as open source database products, such as MySQL and PostgreSQL, grow in popularity and are developed by virtual teams rather than large corporations.
SQL in a Nutshell, Second Edition, explains the SQL implementation of five popular RDBMSs. These vendors do not meet all the SQL2003 standards; in fact, all RDBMS platforms play a constant game of tag with the standards bodies. Many times, as soon as vendors close in on the standard, the standards bodies update, refine, or otherwise change the benchmark. Conversely, the vendors often implement new features that are not yet a part of the standard.
1.2.4 SQL2003 Statement Classes
Comparing statement classes further delineates SQL2003 from SQL92. However, you will still hear these terms bantered about. So you need to know them. In SQL92, SQL statements are grouped into three broad categories:
In contrast, SQL2003 supplies seven core categories, now called classes, that provide a general framework for the types of commands available in SQL. These statement "classes" are slightly different than the SQL92 statement categories, since they attempt to identify the statements within each class more accurately and logically. Furthermore, because SQL is constantly under development, new features and commands enter the standard and may necessitate new statement classes. So, SQL2003 has a new sets of statement classes, originally added in the SQL99 standard, making them somewhat more comprehensible and logical. Additionally, the new statement classes now allow some "orphaned" statements-which did not fit well into any of the old categories-to be properly classified.
Table 1-1 identifies the SQL2003 statement classes and lists some commands in each class, each of which is fully discussed later. At this point, the key is to remember the statement class title.
Those who work with SQL regularly should become familiar with both the old (SQL92) and the new (SQL2003) statement classes, since both nomenclatures are still used to refer to SQL features and statements.
|Table of Contents|