Database Engines and Ansi SQL Compliance

Why does no database fully support ANSI or ISO SQL standards?

In the software industry you have some standards that are really standards, i.e., products that don't comply with them just don't work. File specifications fall into that category. But then you also have "standards" that are more like guidelines: they may defined as standards with point-by-point definitions, but routinely implemented only partially or with significant differences. Web development is full of such "standards", like HTML, CSS and "ECMAScript" where different vendors (i.e. web browsers) implement the standards differently.

The variation causes headaches, but the standardization still provides benefits. Imagine if there were no HTML standard at all and each browser used its own markup language. Likewise, imagine if there were no SQL standard and each database vendor used its own completely proprietary querying language. There would be much more vendor lock-in, and developers would have a much harder time working with more than one product.

So, no, ANSI SQL doesn't serve the same purpose as ANSI standards do in other industries. But it does serve a useful purpose nonetheless.

Is 'INDEX' valid SQL ANSI ISO standard keyword / reserved word?

There is no ANSI standard for SQL language used to create, alter, or manage indexes. So no, INDEX is not a keyword (reserved word) per ANSI standards.

Kevin Kline specifically backs me up here in his book SQL in a Nutshell. He points this out as one of the reasons the syntax for creating indexes varies greatly among vendors.

As further circumstantial evidence you'll also note a variety of vendors mention in their SQL documentation that statements regarding INDEXs are extensions to the ANSI standard. For example see IBM doc here for ALTER INDEX.

This is also a handy list of the ANSI SQL reserved words - but only up to 2003.

---

As a side note: the only time I've ever seen ANSI mentioned at all with regards to indexes is when talking about how a unique index (often simply a unique constraint) treats null values. According to ANSI a null does not equal a null; therefore a unique index should allow multiple null values since per ANSI they do not equal each other. Some engines follow this rule - others do not. The ANSI standard in this case only refers to whether two nulls are equal or unique... the standard has nothing to do with the index. There may be other ANSI standards that have a similar effect on INDEX but nothing regarding the DDL surrounding them.

list aggregation

AFAIK, NO, those are RDBMS specific implementation as you have already noted down (or) GROUP_CONCAT() in case of MySQL. Moreover, ANSI SQL is a standard and not a product by itself.

---

Related Topics