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

Question

If I were designing a oil refinery, I wouldn't expect that materials from different vendors would not comply with published standards in subtle yet important ways. Pipework, valves and other components from one supplier would come with flanges and wall thicknesses to ANSI standards, as would the same parts from any other supplier. Interoperability and system safety is therefore assured.

Why then are the common databases so choosy about which parts of the standards they adhere to, and why have no 100% standards-compliant systems come to the fore? Are the standards 'broken', lacking in scope or too difficult to design for?

Taking this to conclusion; what is the point of ANSI (or ISO) defining standards for SQL?

Edit: List of implementation differences between common databases

Woden

Would you care to back up those assertions with hard facts? Please list, for each major vendor, how their product does not comply with the standard. – Sculley

Indeed.. Who is not compliant? Or are you saying that people who provide more than the spec are doing something wrong? – Consciencestricken

It's pretty much common knowledge. Database do not implement the entire SQL spec. I believe a few fully implement SQL92. I'm not aware of any that implement SQL99 or newer. There are plenty of reasons why this is the case, but honestly, the takeaway point is simply that SQL isn't much of a standard, and hasn't been for some 15 years. – Cadelle

So, to my final point, why even bother publishing a standard that no-one adheres to? – Woden

@Pax I'm no DB expert, just an interested outsider, so I can't quote chapter and verse, but there is plenty of evidence on view in even a cursory search. I also found this resource: troels.arvin.dk/db/rdbms – Woden

@Darren Clark A superset of the standards would not be a problem, it's the sometimes fundamental differences that surprise me. For instance, the handling of dates and times differs greatly between DBs, and I think most would agree that almost all DBs rely heavily on dates, times and the differences between them. – Woden

Answer 1

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.

Radiation

HTML-standard - which one ? Wich version ? You mean a standard to deviance from XML conformance ? And a common video/audio media codec ? Or you mean that a browser that "supports HTML5" actually supports ALL of HTML5 ? The HTML-standard is a joke. It's not even a standard, it's a recommendation. And so is the interpretation of the SQL "standard". After all, if there were full compliance, you couldn't vendor-lockin people, both in HTML and SQL. There is no point in such "standards". Even a slight deviation can have massive consequences. – Madewell

@StefanSteiger, I think your point about SQL is more-or-less in line with the OP's, and your point about HTML is more-or-less in line with what I said (may be defined as standards...implemented only partially or with significant differences). You just reach a different conclusion. You say there's no point. I say I'd rather have HTML and SQL at least semi-consistently defined than not at all. – Radiation

Answer 2

Probably because standards conformance is of a low priority to database system purchasers. They are more interested in:

• compatibility with what they've already got
• performance
• price
• OS support

to name but a few factors.

The same is true of programming languages - very few (if any) compilers support every single feature of the current ANSI C and C++ standards.

As to why bother with standard, well most vendors do eventually bring standard support on board. For example, most vendors support more or less all of SQL89. This allows the vendor to tick a (relatively unimportant) check-box on their spec sheet and also allow people like me who are interested in writing portable code to do so, albeit having to forgo lots of bells and whistles.

Asperse

Indeed. You have a choice of using only the ANSI standard features and being able to switch databases more easily, or use all the features of your chosen database and be a lot more productive. For many people who have no intention of switching database providers the sensible choice is productivity. At which point, it doesn't matter whether it's standards compliant or not because you're already committed to non-standard features. – Triley

I don't think it's quite the same situation as for C/C++. You're right that few compilers actually support the entire C++ language, but it's at least recognized as the right direction to go, and customers generally kick up a fuss if their compiler doesn't support the language features they need. In databases, users just accept nonconformance to a much greater extent. That's just how the world works, and no one really seems to mind. – Cadelle

@jalf yes, I think this is probably becuase procedural language programmers tend to have a much more "language lawyer" kind of outlook than do database guys - if a C++ programmer sees something in the standard, he wants it - now, whereas I've met lots of database guys (particularly DBAs) who probably aren't even aware there are ANSI SQL standards. – Asperse

@anon: It's true, but it is really weird, isn't it? Coming from a C++ background to SQL and Oracle PL/SQL, I'm tempted to write long rants to database providers about their lack of standard support. However, since Oracle won't give a damn about my opinion it is not worth the trouble... (and I don't think free databases deserve the rant; they're free after all) – Scotney

Now that Oracle bought MySQL from Sun, I wonder if they will bring MySQL and Oracle dialects together. It would be stupid not to do so (when backwards compatibility doesn't get in the way), but I think Oracle simply does not care... I would love to be proved wrong, tho. – Scotney

Answer 3

See the article "IS SQL A REAL STANDARD ANYMORE?" for a discussion about the current (2005) issues of the SQL standard.

Gauntlet

I think this is the right answer. On pages 2 and 3: NIST used to test conformance and the US government could only buy conforming implementations. When NIST started to dismantle its data management standards program in 1996, database vendors did not bother to implement the standard anymore. – Scotney

The paper in the URL shows database like Progress, Bull or Tandem that are not relational databases, but only hierarchical or netweok databases made of files, records and pointers... But SQL language as been made especially for relational databases. Also the distance from the standard does not show that the standard is regressing, but on the contrary that it is moving towards its objectives and deserves to be better respected by Relational DBMSs. – Obtest

Answer 4

Indeed, the ANSI SQL standard is not often followed. Just read SO: most SQL threads never refer to the standard while, for instance, discussions on network protocols often include the actual quote, chapter and verse of the relevant RFC.

I always suspected that one of the reasons is the fact that the SQL standard is not freely distributable. Simply getting it is not trivial. Various unofficial copies float around.)

Another reason is that it is a very complicated text and poorly organized. It uses a strange vocabulary (such as "authID" instead "user"). You need books just to understand the standard ("A guide to the SQL standard", C.J. Date, Hugh Darwen - Addison-Wesley).

Exact

Good point - I always wonder how you can call something a standard if people cannot simply look up the specification. You only need to look at the story about ISO's specification of Microsoft's OpenDocument format to see that the whole ISO standardization process is a joke. – Strafe

The C++ standard has some of those problems too (hard to read; the official version is not free), but programmers and compiler developers seem to discuss the standard very often, and write articles about it so that even novices can have a basic understading of what is and what is not in the standard language. – Scotney

Answer 5

Mimer SQL has great standards support, yet it is pretty unknown. It is in production in several large sites, mostly in Sweden. But I think a lot of sites are migrating to others.

Detailed support statments:

• SQL-99
• SQL-2003
• PSM
• Database triggers and functions according to SQL:1999
• Binary and Character Large OBjects (BLOB/CLOB/NCLOB) support according to SQL:1999
• Multi-database transactions (two-phase commit) conforming to Open Group's XA-standard
• Support for Java ME CDC Foundation Profile and Java ME CLDC/MIDP

Gauntlet

Answer 6

I don't know the history of ANSI SQL specifically. But it seems that many times in software development, standards are written after the major players have already implemented their own proprietary versions of things. Once a company is invested in its own way of doing things, it's really hard to justify changing or removing features people have come to rely on just to adhere to a standard. Web standards are a primary example of this phenomenon.

Confluent

Agreed. But this is also true in many other spheres, engineering included. Often a novel approach or market clout is enough to define a de-facto standard that in time becomes ratified by a standards body and then becomes the standard, observed universally. "IT" seems very bad at not fully closing the loop on the last part. – Woden

Answer 7

A few years ago, one of the worst industries as far as pipes and connectors being mutually compatible was firefighting equipment. There were literally dozens of mutually incompatible hose to pump connections. When mutual aid became commonplace among fire fighters, they had to bring along dozens of adaptors to be able to interoperate their equipment.

On September 11, both police and firefighters had private wireless networks for intercommunicating among their people. But, guess what? The two systems were not mutually compatible. So there were needless delays in communicating information from one kind of public safety servant to another.

If you go back far enough, you can find a time in New York City where about half the electric grid was DC, favored by Edison, and the other half was AC, favored by Westinghouse.

Standards sometimes come about by themselves, and are called de facto standards. More commonly, standards have to be set forth by a body specifically empowered to make it happen. As to the SQL standards, some of the largest vendors pre date the standard. In order to comply with the standard, they would have to put in engineering expense that doesn't benefit their existing client base. Worse yet, they might end up being incompatible with their own prior product.

Full compliance with the SQL standard might yet happen, but it's unlikely. Even if it does, there's a delay time between the evolution of a new SQL standard and compliance with it.

Snowcap

Answer 8

The real reason: most "developers" are client-centric coders, and therefore neither understand nor care about Dr. Codd's 12 rules. This is also why MySql, which isn't a relational database in any significant manner, is frequently seen in webKiddie development. Such developers only want rudimentary SELECT, UPDATE, DELETE parsing. They eschew constraints of any kind, preferring to "do it in the application". Reactionary 1960's applications are what you get.

Ostrich

Answer 9

IMHO, the DB vendors push forward the ANSI SQL standards to include new features & constructs within their field much more than ANSI telling the DB vendors the "one true way".

The DB market is driven by features, scalability and cost. It is not a commercial priority to forego and delay a technical advantage (i.e. partitioning, pivot, UPSERT, replication) by waiting for ANSI to ratify the syntax. By the time that has been done, there is already a significant installation of the proprietary syntax.

That being said, most DB vendors have improved their core "ANSI SQL" support greatly in the last few years. (SQL Server with the SELECT FROM INFORMATION_SCHEMA and Oracle's ANSI joins actually working as well as native joins under the CBO)

Syzygy

Answer 10

According to the HSQLDB manual, it is the most standards compliant RDBMS.

• Almost all syntactic features of SQL-92 up to Advanced Level are supported
• SQL:2008 core and many optional features of this standard

Zoraidazorana

Answer 11

Most of them are pretty compliant. But here's the bad news, IMO - standards breed mediocrity. Vendors want you to get locked into their extensions, and there's often good reason to do to nonstandard things. Realistically, how likely are you to dump Oracle for SQL Server, or vice-versa? Unless you build a product that your cusotmers may use against other databases, you as an enterprise are unlikely to swap out DB's. Too painful.

Bulgarian

Answer 12

Companies usually tend to use one vendor to avoid having a jungle of different and possibly incompatible systems to support. It's also a lot cheaper to train your developers/system engineers in using one database vendor's tools than in 3 different sets of tools. Later on, this company may then grow large enough to buy some of its competitors. This would mean another entirely different set of tools that you'd have to manage, integrate, etc.

That's a lot of work.

Imagine that instead of that, you have a portability layer so that you really don't care what's beneath. That would be a lot better.

Undermanned