3 ms·

SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI

SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99. Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features. In other words, SQL databases are only trivially interchangeable for trivial cases.

gregjor 14y ago
I agree that there are numerous inconsistencies. I just have a different definition of "trivial cases" than you do. The kinds of things you mention -- real issues that come up in real applications -- fall outside of what most databases are doing in the kinds of web apps that I work on. I haven't had to deal with Oracle vs. SQL Server bulk load problems or Oracle's unique implementation of NULLs in years. Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases.
- einhverfr 14y ago
  >"Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases." If this is the case, then, it's only because database application developers go out of their way to write compatible code. Things like upserts are not trivially compatible between vendors, and so are avoided by people trying to write portable applications. Similarly, let's look at SQL 2003 structure data type inheritance and how this is implemented in Informix, Oracle, DB2, and PostgreSQL. Ok, so it isn't in PostgreSQL, and Informix allows tables to be inherited as well as types, and will even happily return result sets with different numbers of columns per row (google "Informix jagged rows")... And Oracle and DB2 look pretty close in this way until you get under the hood.

gregjor 14y ago

I agree that there are numerous inconsistencies. I just have a different definition of "trivial cases" than you do. The kinds of things you mention -- real issues that come up in real applications -- fall outside of what most databases are doing in the kinds of web apps that I work on. I haven't had to deal with Oracle vs. SQL Server bulk load problems or Oracle's unique implementation of NULLs in years. Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases.

einhverfr 14y ago
>"Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases." If this is the case, then, it's only because database application developers go out of their way to write compatible code. Things like upserts are not trivially compatible between vendors, and so are avoided by people trying to write portable applications. Similarly, let's look at SQL 2003 structure data type inheritance and how this is implemented in Informix, Oracle, DB2, and PostgreSQL. Ok, so it isn't in PostgreSQL, and Informix allows tables to be inherited as well as types, and will even happily return result sets with different numbers of columns per row (google "Informix jagged rows")... And Oracle and DB2 look pretty close in this way until you get under the hood.