Database Reference
In-Depth Information
The Impact of Standards
If all databases are SQL99-compliant, then they must be the same. At least that's often the assumption. In this section,
I'd like to dispel that myth.
SQL99 is an ANSI/ISO standard for databases. It was the successor to the SQL92 ANSI/ISO standard, which in
turn superseded the SQL89 ANSI/ISO standard. It has now been superseded itself by the SQL2003, SQL2008, and
SQL2011 standards updates. The standard defines a language (SQL) and behavior (transactions, isolation levels,
and so on) that tell you how a database will behave. Did you know that many commercially available databases are
SQL99-compliant to at least some degree? Did you also know that it means very little as far as query and application
portability goes?
Starting with the SQL92 standard, the standards have four levels:
•
Entry-level:
This is the level to which most vendors have complied. It is a minor enhancement
of the predecessor standard, SQL89.
No database vendors have been certified higher
and, in
fact, the National Institute of Standards and Technology (NIST), the agency that used to certify
for SQL-compliance, does not even certify anymore. I was part of the team that got Oracle 7.0
NIST-certified for SQL92 entry-level compliance in 1993. An entry level-compliant database
has a feature set that is a subset of Oracle 7.0's capabilities.
•
Transitional:
This level is approximately halfway between entry level and intermediate level as
far as a feature set goes.
•
Intermediate:
This level adds many features including (this is not by any means an exhaustive list)
•
Dynamic SQL
DELETE
for referential integrity
•
DATE
and
TIME
data types
•
Cascade
•
Domains
•
Variable length character strings
CASE
expression
•
CAST
functions between data types
•
A
•
Full:
Adds provisions for (again, this list is not exhaustive)
•
Connection management
BIT
string data type
•
A
•
Deferrable integrity constraints
FROM
clause
•
Derived tables in the
CHECK
constraint clauses
•
Subqueries in
•
Temporary tables
The entry-level standard does not include features such as outer joins, the new inner join syntax, and so on.
Transitional does specify outer join syntax and inner join syntax. Intermediate adds more, and Full is, of course all of
SQL92. Most topics on SQL92 do not differentiate between the various levels, which leads to confusion on the subject.
They demonstrate what a theoretical database implementing SQL92 full would look like. It makes it impossible to pick
up a SQL92 book, and apply what you see in the topic to just any SQL92 database. The bottom line is that SQL92 will
not go very far at the entry level and, if you use any of the features of intermediate or higher, you risk not being able to
port your application.
