Published in TDAN.com January 2003
Do IBM, Microsoft, and Oracle support the SQL:1999 standard? And will they support the SQL:2003 standard as well?
The Committee
There is an international committee working on the SQL standard (ISO/IEC JTC 1/SC 32/WG 3) as well as an American committee (ANSI TC NCITS H2) – I’ll call them The Committee for short. The Committee has published several editions of the official standard for SQL. The three editions that matter today are:
- SQL-92 the previous standard
- SQL:1999 the current standard, also called SQL-99
- SQL:2003 the future standard, also called SQL:200n
The Big Three DBMS vendors (IBM, Microsoft, Oracle) have representatives on The Committee, and claim to support the official standard. Common support for an official standard is wonderful for both users and vendors, in the same way that common languages and unambiguous specifications are wonderful. I want to show where The Big Three support the standard, and where they don’t. You can use my description to make your code more portable (by following the tips), to make yourself more portable (by knowing what transferable skills you have), or to make your vendor more portable (by insisting on standards as a project criterion).
Vendor Claims
Each vendor makes a formal but cagey statement about standard SQL in its official documentation, which I quote:
“DB2 UDB SQL is consistent with the SQL 1999 Core standard.”
— IBM Corporation, SQL Reference For Cross-Platform Development
“The Core SQL:1999 features that Oracle fully supports are listed in Table B-1 …”
— Oracle Corporation, Oracle9i SQL Reference
“The Microsoft SQL Server 2000 Transact-SQL version complies with the Entry level of the SQL-92 standard …”
— Microsoft Corporation, SQL Server 2000 Books Online (Updated)
Here’s one more quote, from the SQL:1999 standard document:
“This [SQL:1999] edition cancels and replaces the third edition [SQL/92].”
— SQL:1999 standard
In other words, The Committee says Microsoft is supporting a non-existent version of the SQL standard. This is a small point, since Entry-Level SQL-92 is very similar to Core SQL:1999. Let’s compare each DBMS to Core SQL:1999.
SQL:1999 has two sets of features: core features and non-core features. There are no “levels” as in SQL-92. The way to look at a DBMS’s standardization is in terms of its features, starting with the core features that it must support and going on to the supported non-core features. This is easy to do because features have identifiers and names. For example, if a DBMS handles roles correctly, the vendor may claim support for “Feature T331 basic roles” (which is non-core).
Feature Notes
I’ll describe each standard feature for IBM DB2 UDB Universal Server 7.2, Microsoft SQL Server 2000, and Oracle9i with a template like this:
| Feature | IBM | Microsoft | Oracle |
| Feature-ID Feature-Name | – | * | ** |
- The Feature-ID is unimportant unless you want to refer to the ANSI/ISO standard. The Feature-Name is an unofficial short descriptor, for example “Roles” or “Triggers” – I expect that most readers will know what those are, so the descriptors are very short.
- A dash (-), as in the IBM column, means that the DBMS does not support the feature.
A single asterisk (*), as in the Microsoft column, means that the DBMS supports the feature but not the syntax; i.e. Microsoft allows role creation with:
- EXEC sp_addrole ‘rolename’
but in standard SQL the syntax is:
- CREATE ROLE rolename
A double asterisk (**), as in the Oracle column, means that the DBMS supports both the feature and the syntax, or something close to it.
The – or * or ** marks are my interpretation of vendor documentation and the SQL:1999 standard, without tests, without confirmation. I have ignored many details which I decided are trivial, and of course others might conclude differently.
The marks show how portable a feature is, and once you’ve looked at many features you’ll have a better idea what “support for ANSI standards” means to the vendors. An individual mark shows if a DBMS is “more standard” with respect to one feature, but no summary is possible. The sampling is not statistically valid.
Core Feature Support
This section shows the exceptional cases where at least one DBMS misses a core SQL:1999 feature. Since core features are more important than non-core features, in this section (only) I will explain each mark.
| Feature | IBM | Microsoft | Oracle |
| E051 AS clause in SELECT | ** | ** | * |
| E071 UNION DISTINCT | * | * | ** |
| E071 EXCEPT DISTINCT | ** | – | * |
| E121 expressions in ORDER BY | – | ** | ** |
| E121 cursors | ** | * | * |
| E131 null value support | ** | ** | – |
| E141 not-null PRIMARY KEY | – | ** | ** |
| E151 SET TRANSACTION | – | * | ** |
| F021 INFORMATION_SCHEMA | * | ** | * |
| F031 ALTER TABLE | ** | * | * |
| F031 DROP TABLE | * | * | * |
| F041 INNER JOIN | * | * | ** |
| F051 DATE & TIME literals | * | * | * |
| F311 CREATE SCHEMA | ** | * | * |
| F812 Basic flagging | * | * | * |
| S011 Distinct data types | ** | – | – |
| E21-04 CHARACTER_LENGTH | * | – | * |
| E21-05 OCTET_LENGTH | * | * | * |
| E21-06 SUBSTRING | * | * | * |
| E21-07 Concatenation with || | ** | * | ** |
| E21-09 TRIM | * | * | * |
| E21-11 POSITION | * | * | * |
- For E051: Oracle disallows “SELECT column AS column” and “FROM table AS table” – it doesn’t like the keyword AS.
- For E071 UNION: IBM and Microsoft disallow “a UNION DISTINCT b.”
- For E071 EXCEPT: Microsoft disallows any form of EXCEPT. Oracle allows the functionality but requires the keyword MINUS rather than EXCEPT.
- For E121 expressions: IBM allows “ORDER BY a + 5” but only if “a + 5” is in the select list.
- For E121 cursors: Microsoft doesn’t support WITH HOLD clauses on cursor declarations.
- Oracle supports them but their scope is too large, they work on ROLLBACK as well as on COMMIT.
- For E131: Oracle won’t distinguish NULLs from blank strings.
- For E141: The statement “CREATE TABLE t (column1 INT, PRIMARY KEY (column1))” is unacceptable to DBMSs that think NOT NULL should be explicit. The standard says the DBMS should infer it. This flaw is common.
- For E151: Microsoft disallows “SET TRANSACTION … READ ONLY.” IBM disallows all SET TRANSACTION statements.
- For F021: IBM and Oracle have metadata but can’t query it with statements like “SELECT * FROM INFORMATION_SCHEMA.TABLES.”
- For F031 ALTER TABLE: Microsoft and Oracle disallow the optional keyword COLUMN in “ALTER TABLE … ADD.”
- For F031 DROP TABLE: All three DBMSs have difficulty with the standard syntax, which must be “DROP TABLE table-name {CASCADE|RESTRICT}.” There are several similar DROP and REVOKE situations.
- For F041: Some support exists for “FROM a [INNER] JOIN b ON …” but “FROM a [INNER] JOIN USING …” is another matter.
- For F051: Given the profusion of choices that all three DBMSs allow, it’s disappointing that TIMESTAMP ‘1999-01-01 12:34:56’ is always illegal. Some trouble also exists with functions like CURRENT_DATE.
- For F311: Minimally, a DBMS should support CREATE SCHEMA with an AUTHORIZATION clause. Only IBM does.
- For F812: A flagger tells users when their SQL statements are standard SQL. The Microsoft and Oracle flaggers fail to show what’s SQL:1999 compliant.
- For S011: IBM allows “CREATE DISTINCT TYPE audio AS BLOB (1M).” Microsoft’s sp_addtype() function does not provide similar functionality.
- For E21-04 to E21-11: Microsoft’s LEN function returns the trimmed length, which I categorize as non-support. The other char functions are all supported but names differ. For example compare IBM’s “SUBSTR(x,1,10)” to SQL:1999’s “SUBSTRING(x FROM 1 FOR 10).”
Non-core Feature Support
Now let’s look at the non-core features. For this chart I selected only features supported by at least one DBMS — if nobody supports a feature, it’s uninteresting no matter how nice it is. Then I excluded features which I’m sure are uninteresting because of impracticality. For what remains, here’s the chart, without notes.
| Feature | IBM | Microsoft | Oracle |
| E141 nullable UNIQUE column | – | * | ** |
| F052 INTERVAL | – | – | ** |
| F191 ON DELETE CASCADE | ** | ** | ** |
| F302 INTERSECT | ** | – | ** |
| F391 long identifiers | – | * | ** |
| F421 NCHAR | – | ** | ** |
| F461 named character sets | – | ** | * |
| F651 catalog name qualifier | – | ** | – |
| F691 collations | – | ** | * |
| F701 ON UPDATE CASCADE | – | ** | – |
| F721 deferrable constraints | – | – | ** |
| S023 basic structured types | ** | – | ** |
| S091 basic ARRAY support | – | – | * |
| T271 savepoint | ** | * | ** |
| T331 basic roles | – | * | ** |
| T041 LOBs | ** | ** | ** |
| T441 ABS & MOD functions | ** | * | ** |
Oracle supports a few more non-core features than IBM or Microsoft. It’s up to you to decide whether any of them are important.
Stored Procedures Support
The vendors’ syntaxes differ in stored procedures more than in ordinary SQL. For an illustration, here is a chart to show what CREATE PROCEDURE looks like in three dialects. I use one line for each significant part, so you can compare dialects by reading across the line.
| SQL:1999 / IBM | Microsoft / Sybase | Oracle |
| CREATE PROCEDURE | CREATE PROCEDURE | CREATE PROCEDURE |
| Sp_proc1 | Sp_proc1 | Sp_proc1 |
| (param1 INT) | @param1 INT | (param1 IN OUT INT) |
| MODIFIES SQL DATA BEGIN |
||
| DECLARE num1 INT; |
AS DECLARE @num1 INT | AS num1 INT; BEGIN |
| IF param1 <> 0 | IF @param1 <> 0 | IF param1 <> 0 |
| THEN SET param1 = 1; | SELECT @param1 = 1; | THEN param1 :=1; |
| END IF | END IF; | |
| UPDATE Table1 SET | UPDATE Table1 SET | UPDATE Table1 SET |
| column1 = param1; | column1 = @param1 | column1 = param1; |
| END | END; |
Oracle supports a few more non-core features than IBM or Microsoft. It’s up to you to decide whether any of them are important.
Stored Procedures Support
The vendors’ syntaxes differ in stored procedures more than in ordinary SQL. For an illustration, here is a chart to show what CREATE PROCEDURE looks like in three dialects. I use one line for each significant part, so you can compare dialects by reading across the line.
— Data from SQL Performance Tuning (Gulutzan and Pelzer, Addison-Wesley, 2002)
The “SQL:1999 / IBM” column shows that the standard and IBM syntaxes are the same (the only tiny differences are that IBM would require an additional LANGUAGE SQL clause and a semicolon after the words “END IF,” which I have not shown). The “Microsoft / Sybase” column shows a noticeably different syntax and ignores the fact that Microsoft now allows you to say SET in place of SELECT, e.g. “SET @param1 = 1.” The “Oracle” column shows yet another syntax.
If Microsoft and Oracle want to support the standard, they will have to copy IBM. At the moment they prefer to point in other directions. Microsoft promises that its next version will have better stored-procedure support for C#; Oracle likes to emphasize Java stored procedures.
SQL:2003 Features
To describe SQL:2003 I’d need a whole series of articles. But I’d better say something introductory since I’m sure there are readers who have yet to hear about it. SQL:2003 is the next version of the standard. It would be safer to call it SQL:200n – most people do – because we’re still unsure that The Committee will publish sometime in 2003. I’m going out on a limb because I believe in The Committee’s current schedule. The big SQL:2003 features are: more collection data types, cleaner object/relational specification, and references to new parts such as XML. The big missing SQL:2003 feature is the SQL:1999 standard BIT data type, but it was confusing and none of The Big Three supported it anyway. I guess it will be little missed.
The next chart notes the SQL:2003 features that at least one DBMS has already implemented. I’m going to be generous in my marking because some syntax details will be modifiable right up to July 2003.
Most of the already-implemented features are scalar functions or set functions of interest to a restricted set of users. Expect a bit more excitement when each vendor puts out its next version.
Skeptics
A former member of The Committee (Joe Celko) has stated on Usenet:
“Seriously, the SQL-99 Standard is going nowhere.”
Another member (Michael Gorman) wrote a critical article titled, “Is SQL A Real Standard Anymore?” justifying this prediction:
“Competition [among DBMS vendors] will drop, prices will rise, and portability of programs, data and trained staff will end.”
The key criticism — assuming I’m not misrepresenting anyone — is that there is no enforcer to ensure that vendors really follow the standard. Without enforcement, fission occurs as it did with Unix. And it’s too easy for a vendor to reap benefit from the standard by claiming compliance without really providing it. (I know of one vendor who does that but I won’t name names because
I believe the claim is due to a misunderstanding.)
On the other hand, progress does occur. Here are some cases of vendors who were non-compliant in the past, but have seen the light.
- IBM has been enthusiastic about making “standard CLI” its native interface, and if you examine the interface you’d be hard put to distinguish it from ODBC.
- For several years, Microsoft had a product that could not store blank strings because they would be confused with NULLs. Microsoft SQL Server 2000 has no such problem.
- Until recently, Oracle insisted on join syntax in the WHERE clause. But Oracle9i works fine with [INNER] JOIN inside the FROM clause.
Almost all the standard features that I’ve discussed in this article are new. A few years ago no vendor supported them. Now at least one does. I don’t pretend that this is always due to pressure from The Committee – perhaps it’s the vendor who influenced the standard rather than vice versa. But sometimes if the mountain won’t go to Mohammed, Mohammed will go to the mountain.
Writing Portable Code
The easy advice for portable code is: use the least common denominator. Here are some smaller bits of advice:
- Say “INSERT INTO” rather than just “INSERT,” which is a Microsoft/Sybase abbreviation.
- Say “10.5E10” rather than “10.5e10.” For some reason a lowercase e is not legal in SQL:1999 floating-point literals. SQL:2003 will fix this oversight.
- Say “DEFAULT 5 NOT NULL” rather than “NOT NULL DEFAULT 5.” In SQL:1999 a column’s default clause must precede constraint clauses.
- Avoid using names that might be reserved keywords in some other dialect. A common recommendation is to use delimited identifiers (identifiers in quotes), but delimited identifiers are supposed to be case sensitive. So just add an underline character at the end of the name. No reserved keyword ever ends with _. For example:
- CREATE TABLE Element … /* bad, SQL:2003 reserved word */
- CREATE TABLE “Element” … /* better but maybe
- “ELEMENT” */
- CREATE TABLE Element_ … /* better */
And when you use a new feature, take into account whether it’s standard and whether multiple DBMSs support it. My charts will help.
References
Michael Gorman, “Is SQL A Real Standard Anymore?”, www.tdan.com/i016hy01.htm
Read along with a rejoinder on usenet, which you can find by searching for “gorman lindawie” on Google Groups.
FTP directory at sqlstandards.org.
Ocelot Computer Services Inc., SQL Book Reviews
This is my annotated list of books on standard, non-vendor-specific, SQL.
Oracle Corporation, Oracle9i Database List Of Books
The SQL Reference Manual, Appendix B, “Oracle And Standard SQL,” is a commendably frank example of how a DBMS vendor should show which SQL:1999 features are supported.
This article was previously published on DBAzine.com, the on-line community for database issues and solutions. Visit DBAzine.com by clicking here.