How Important is SQL Portability?

Question

It seems to me, from both personal experience and SO questions and answers, that SQL implementations vary substantially. One of the first issues for SQL questions is: What dbms are you using?

In most cases with SQL there are several ways to structure a given query, even using the same dialect. But I find it interesting that the relative portability of various approaches is frequently not discussed, nor valued very highly when it is.

But even disregarding the likelihood that any given application may or not be subject to conversion, I'd think that we would prefer that our skills, habits, and patterns be as portable as possible.

In your work with SQL, how strongly do you prefer standard SQL syntax? How actively do you eschew propriety variations? Please answer without reference to proprietary preferences for the purpose of perceived better performance, which most would concede is usually a sufficiently legitimate defense.

Answer 1

I vote against standard/vendor independent sql

• Only seldom the database is actually switched.
• There is no single database that fully conforms to the current sql standard. So even when you are standard conform, you are not vendor independent.
• vendor differences go beyond sql syntax. Locking behaviour is different. Isolation levels are different.
• database testing is pretty tough and under developed. No need to make it even harder by throwing multiple vendors in the game, if you don't absolutly need it.
• there is a lot of power in the vendor specific tweaks. (think 'limit', or 'analytic functions', or 'hints' )

So the quintessence: - If there is no requirement for vendor independence, get specialised for the vendor you are actually using. - If there is a requirement for vendor independence make sure that who ever pays the bill, that this will cost money. Make sure you have every single rdbms available for testing. And use it too - Put every piece of sql in a special layer, which is pluggable, so you can use the power of the database AND work with different vendors - Only where the difference is a pure question of syntax go with the standard, e.g. using the oracle notation for (outer) joins vs the ANSI standard syntax.

Answer 2

We take it very seriously at our shop. We do not allow non-standard SQL or extensions unless they're supported on ALL of the major platforms. Even then, they're flagged within the code as non-standard and justifications are necessary.

It is not up to the application developer to make their queries run fast, we have a clear separation of duties. The query is to be optimized only by the DBMS itself or the DBAs tuning of the DBMS.

Real databases, like DB2/z :-), process standard SQL plenty fast.

The reason we enforce this is to give the customer choice. They don't like the idea of being locked into a specific vendor any more than we do.