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.

A few elements of the Core in SQL99 have been deleted from SQL2003, including:

• The BIT and BIT VARYING datatypes

• The UNION JOIN clause

• The UPDATE...SET ROW statement

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:

Elementary OLAP functions

Adds an Online Analytical Processing (OLAP) amendment, including a number of windowing functions to support widely used calculations such as moving averages and cumulative sums. Windowing functions are aggregates computed over a window of data: ROW_NUMBER , RANK , DENSE_RANK , PERCENT_RANK , and CUME_DIST . OLAP functions are fully described in T611 of the standard. Some database platforms are starting to support the OLAP functions. Refer to Chapter 4 for details.

Sampling

Adds the TABLESAMPLE clause to the FROM clause. This is useful for statistical queries on large databases, such as a data warehouse.

Enhanced numeric functions

Adds a large number of numeric functions. In this case, the standard was mostly catching up with the trend in the industry, since the new functions are already supported by one or more of the database platforms. Refer to Chapter 4 for details.

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.

Part 1 - SQL/Framework

Includes common definitions and concepts used throughout the standard. Defines the way the standard is structured and how the various parts relate to one another. Describes the conformance requirements set out by the standards committee.

Part 2 - SQL/Foundation

Includes the Core, an augmentation of the SQL99 Core, and is the largest and most important part of the standard.

Part 3 - SQL/CLI (Call-Level Interface)

Defines the call-level interface for dynamically invoking SQL statements from external application programs. SQL/CLI also includes over 60 routine specifications to facilitate the development of truly portable shrink-wrapped software.

Part 4 - SQL/PSM (Persistent Stored Modules)

Standardizes procedural language constructs similar to those found in database platform-specific SQL dialects like PL/SQL and Transact -SQL.

Part 9 - SQL/MED (Management of External Data)

Defines the management of data located outside of the database platform using datalinks and a wrapper interface.

Part 10 - SQL/OBJ (Object Language Binding)

Describes how to embed SQL statements in Java programs. It is closely related to JDBC, but offers a few advantages over JDBC. It is also very different from the traditional host language binding possible in early versions of the standard.

Part 11 - SQL/Schemata

Defines over 85 views (three more than in SQL99) used to describe the metadata of each database and stored in a special schema called INFORMATION_SCHEMA . A number of views that existed in SQL99 have been updated.

Part 12 - SQL/JRT (Java Routines and Types)

Defines a number of SQL routines and types using the Java programming language. Features of Java, such as Java static methods and Java classes, are now supported.

Part 14 - SQL/XML

Adds a new type, called XML, four new operators ( XMLPARSE , XMLSERIALIZE , XMLROOT , and XMLCONCAT ), several new functions (described in Chapter 4), and the new IS DOCUMENT predicate. It also includes rules for mapping SQL-related elements (like identifiers, schemas, and objects) to XML-related elements.

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:

Data Manipulation Language (DML)

Provides specific data-manipulation commands such as SELECT , INSERT , UPDATE , and DELETE .

Data Definition Language (DDL)

Contains commands that handle the accessibility and manipulation of database objects, including CREATE and DROP .

Data Control Language (DCL)

Contains the permission-related commands GRANT and REVOKE .

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.

Table 1-1. SQL2003 statement classes

Class	Description	Example commands
SQL connection statements	Start and end a client connection	CONNECT , DISCONNECT
SQL control statements	Control the execution of a set of SQL statements	CALL , RETURN
SQL data statements	May have a persistent and enduring effect upon data	SELECT , INSERT , UPDATE , DELETE
SQL diagnostic statements	Provide diagnostic information and raise exceptions and errors	GET DIAGNOSTICS
SQL schema statements	May have a persistent and enduring effect on a database schema and objects within that schema	ALTER, CREATE, DROP
SQL session statements	Control default behavior and other parameters for a session	SET statements like SET CONSTRAINT
SQL transaction statements	Set the starting and ending point of a transaction	COMMIT, ROLLBACK

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.