Everything in Details: DBMS Implementations

One book cannot possibly cover all existing database implementations, and taking into consideration all these aspects, we've decided to concentrate on "the big three": Oracle Database, IBM DB2 UDB, and Microsoft SQL Server. These implementations have many common characteristics: They are all industrial-strength enterprise level relational databases (relational database model and SQL standards are covered later in this chapter), they use Structured Command Language (SQL) standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), and all three are able to run on Windows operating system. Oracle also is available on virtually any UNIX flavor, Linux, MVS, and OpenVMS; DB2 UDB is running on UNIX/Linux, NUMA-Q, MVS, OS/2, and AS/400.

Note 

ANSI is a private, nonprofit organization that administers and coordinates the U.S. voluntary standardization and conformity assessment system. The Institute's mission is to enhance both the global competitiveness of U.S. business and the U.S. quality of life by promoting and facilitating voluntary consensus standards and conformity assessment systems, and safeguarding their integrity. ANSI was founded October 18, 1918 and is the official U.S. representative to the International Organization for Standardization (ISO) and some other international institutions.

The problem is, none of the databases mentioned earlier is 100 percent ANSI SQL compliant. (We'll talk about three levels of conformance on the following pages; the feature compliance list is given in Appendix J.) Each of these databases shares the basic SQL syntax (though some diversity exists even there), but the language operators, naming restrictions, internal functions, datatypes (especially date and time related), and procedural language extensions are implemented differently.

Cross-References 

See Chapter 14 for more information on the SQL procedural extensions.

Table 1-1 compares some data on maximum name lengths supported by different database implementations.

Table 1-1: Maximum Name Length Restrictions for Some of Database Objects

	IBM DB2 8.1	MS SQL Server 2000	Oracle 9i
Column name length (characters)	30	128	30
Constraint name length (characters)	18	128	30
Index name length (characters)	128	128	30
Number of table columns	255	1023	1000

In an ideal world the standards would rule supreme, and SQL would be freely shared among different implementations for the benefit of humanity. Unfortunately, the reality looks somewhat different. While it is possible to distill a standard SQL understood by all database vendors' products, anything above some very trivial tasks would be better, quicker accomplished with implementation-specific features.