The Big Eight

"In theory there is no difference between theory and practice. But, in practice, there is."

Jan L.A. van de Snepscheut

You don't have to specialize to find useful ideas in this book. Over and over again, we have found that basic matters that are true for DBMS #1 are also true for DBMS #2, DBMS #3, and so on, across the board. For example, we can say that "DBMSs store data in fixed- size pages and the size is a power of two." But isn't that a bold generalization? Let's be honest. We have not checked this statement on every DBMS that exists, we know it's not a law of nature, and in fact we know of at least two DBMSs for which the statement is false. But, in this book, when we make a claim for "all DBMSs" or just "DBMSs" we're not being vague or generalwe mean, very specifically , eight particular DBMSs that we have actually tested and for which we guarantee that the statement is true at time of writing. We call these DBMSs "the Big Eight"not a standard term , but a convenient way to direct your memory to this introductory explanation.

We chose the Big Eight according to the following criteria:

• The DBMS must be an SQL client/server DBMS. DBMSs of other types were excluded.

• The DBMS must have at least a 1% share of the market in North America and Europe according to published and verifiable surveys, or it must be widely known because it's open source.

• The DBMS must support Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC).

We want to emphasize that no DBMS got on our list due to its quality. We chose each of the Big Eight based only on the probability that you'll encounter it or something very much like it. Because the Big Eight have a combined market share of over 85%, with the open source DBMSs having been downloaded hundreds of thousands of times from Internet sites, we're confident that you'll be dealing with one of the Big Eight at least 90% of the time. Table 1-1 shows which DBMSs were tested for this book.

When researching the material for this book, we installed the latest versions of each DBMS available for the MS WindowsNT platform at the time of writing. Each DBMS was installed and tested using the default systems recommended in the vendors ' instructions, except as indicated in the following section, "Installation Parameters." In some cases, we used evaluation copies or personal editions; in no case did we test with extra-cost or little-used optionsour policy was to ignore nondefault installation options, settings, and switches.

Table 1-1. The Big Eight

Short Name	Product Name & Version	Remarks
IBM	IBM DB2 Universal Database 7.2
Informix	IBM Informix Dynamic Server 9.3	Now owned by IBM
Ingres	Ingres II 2.5	Owned by Computer Associates International
InterBase	InterBase 6.0	Open source version. Owned by Borland Software Corporation
Microsoft	Microsoft SQL Server 2000
MySQL	MySQL 3.23	Open source. Owned by MySQL AB
Oracle	Oracle 9i
Sybase	Sybase ASE 12.5

We have avoided endorsing or denouncing any vendor, because our object is to help you improve your SQL, given the hand you've been dealt.

There are test results throughout this book; however, there are no absolute performance figures or inter-DBMS comparisons. There are two reasons for this. One is obvious: such figures wouldn't fit the purpose of the book. The other is interesting: three of the Big EightInformix, Microsoft, and Oraclehave end- user license agreements that specifically prohibit publication of benchmarks.

Installation Parameters

As indicated earlier, in order to minimize our use of extra-cost or little-used options and to level the playing field between DBMSs as much as possible, we installed the Big Eight using the default systems recommended in the vendors' instructions except in the following cases:

• For the sake of consistency, we wanted to run all our tests using the same character setthe Windows 1252 code pagefor every DBMS if at all possible. We chose this code page because we were testing on a Windows NT system and wanted to utilize international character set possibilities.

• We wanted to test the differences between dictionary and binary sorts for every DBMS if at all possible.

For IBM, these criteria meant that the IBM database was created with the default "IBM-1252" character set and default "Local Alphabet" dictionary sort order. We used CHAR columns to test dictionary sorts and CHAR FOR BIT DATA columns to test binary sorts. IBM doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options.

For Informix, these criteria meant that Informix was installed with the default "EN_US 8859-1" for client and server locales, and nondefault "EN_GB.8859-1" db_locale, which provides a dictionary sort order for NCHAR columns. We used NCHAR columns to test dictionary sorts and CHAR columns to test binary sorts. Informix doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options.

For Ingres, these criteria meant that the Ingres database was created with the default "WIN1252" character set and the nondefault "lmulti" dictionary sort order. We used CHAR columns to test dictionary sorts and BYTE columns to test binary sorts. Ingres doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options.

For InterBase, these criteria meant that the InterBase database was created with DEFAULT CHARACTER SET WIN1252 . We used NCHAR columns with COLLATE EN_US to test dictionary sorts and NCHAR columns with no COLLATE clause to test binary sorts. We also used NCHAR columns with (a) COLLATE DA_DA to test Danish/Norwegian sorts, (b) COLLATE DE_DE to test German sorts, (c) COLLATE IS_IS to test Icelandic sorts, (d) COLLATE EN_UK to test Irish sorts, (e) COLLATE ES_ES to test Spanish sorts, and (f) COLLATE FI_FI and COLLATE SV_SV to test Swedish/Finnish sorts.

For Microsoft, these criteria meant that SQL Server was installed with the default "1252/ISO" Character Set and the default "Dictionary Order, case insensitive" Sort Order for CHAR columns, and a nondefault "Binary" Unicode Collation. We used CHAR columns with no COLLATE clause to test dictionary sorts and CHAR columns with COLLATE SQL_Latin1_General_BIN to test binary sorts. We also used CHAR columns with (a) COLLATE SQL_Danish to test Danish/Norwegian sorts, (b) COLLATE German_PhoneBook to test German phone book sorts, (c) COLLATE SQL_Icelandic to test Icelandic sorts, (d) COLLATE Mexican_Trad_Spanish and COLLATE Modern_Spanish to test Spanish sorts, and (e) COLLATE SQL_SwedishStd to test Swedish/Finnish sorts. (Note: Where applicable , PREF was always indicated, the code page was 1252 , case sensitivity was CI , and accent sensitivity was AS .)

For MySQL, these criteria meant that MySQL was installed with the default "Latin1" (aka iso_1) character set. We used CHAR columns to test dictionary sorts and CHAR BINARY columns to test binary sorts. MySQL doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options.

For Oracle, these criteria meant that Oracle was installed with the default "WIN1252" character set. We used CHAR columns with NLS_SORT=XWEST_EUROPEAN to test dictionary sorts and CHAR columns with NLS_SORT=BINARY to test binary sorts. We also used CHAR columns with (a) NLS_SORT=DANISH and NLS_SORT=NORWEGIAN to test Danish/Norwegian sorts, (b) NLS_SORT=XGERMAN to test German dictionary sorts, (c) NLS_SORT=GERMAN_DIN to test German phone book sorts, (d) NLS_SORT=ICELANDIC to test Icelandic sorts, (e) NLS_SORT=XWEST_EUROPEAN to test Irish sorts, (f) NLS_SORT=XSPANISH to test Spanish Traditional sorts, (g) NLS_SORT=SPANISH to test Spanish Modern sorts, and (h) NLS_SORT=FINNISH to test Swedish/Finnish sorts.

For Sybase, these criteria meant that Sybase was installed with the nondefault "Character Set = iso_1" and nondefault "Sort Order = Dictionary." We used CHAR columns to test dictionary sorts and BINARY columns to test binary sorts. Sybase doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options.