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 standard 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 the entry level and the 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
•\ Cascade DELETE for referential integrity
•\ DATE and TIME datatypes
•\ Domains
•\ Variable-length character strings
•\ A CASE expression
•\ CAST functions between datatypes
•\ Full: This level adds provisions for (again, this list is not exhaustive)
•\ Connection management
•\ A BIT string datatype
•\ Deferrable integrity constraints
•\ Derived tables in the FROM clause
•\ Subqueries in CHECK constraint clauses
•\ 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 books 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 book 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.
SQL99 defines only two levels of conformance: Core and Enhanced. It attempted to go far beyond traditional SQL and introduced object-relational constructs (arrays, collections, etc.). It covered a SQL MM (multimedia) type, object-relational types, and so on. No vendors are certifying databases to be SQL99 Core or Enhanced “compliant,” and, in fact, I know of no vendor who is even claiming their product is fully compliant with either level of conformance.
You should not be afraid to make use of vendor-specific features—after all, you are paying a lot of money for them. Every database has its own bag of tricks, and you can always find a way to perform a given operation in each database. Use what is best for your current database, and reimplement components as you go to other databases. Use good programming techniques to isolate yourself from these changes. The same techniques are employed by people writing OS-portable applications—such as the Oracle kernel developers.