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

There is no clear answer whether SQL portability is desirable or not - it really depends a lot on the situation, such as the type of application.

If the application is going to be a service - ie there will only ever be you hosting it, then obviously nobody but you will care whether your SQL is portable enough, so you could safely ignore it as long as you have no specific plans to drop support for your current platform.

If the application is going to be installed at a number of sites, which each have their own established database systems, obviously SQL portability is very important to people. It lets you widen your potential market, and may give a bit of piece of mind to clients who are on the fence in regards to their database system. Whether you want to support that, or you are happy selling only to, for instance, Oracle customers, or only to MySQL/PostgreSQL customers, for example, is up to you and what you think your market is.

If you are coding in PHP, then the vast majority of your potential customers are probably going to expect MySQL. If so, then it's not a big deal to assume MySQL. Or similarly if you are in C#/.NET then you could assume Microsoft SQL Server. Again, however, there is a flip side because there may exist a small but less competitive market of PHP or .NET users who want to connect to other database systems than the usual.

So I would largely regard this as a market research question, unless as in my first example you are providing a hosted service where it doesn't matter to users, in which case it is for your own convenience only.

Answer 2

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 3

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.

Answer 4

In my experience, query portability turns out to be not so important. We work with various data sources (mainly MSSQL and MySQL), but we know which data is stored where and can optimize accordingly. Since we control the systems, we decide when - if ever - structures are moved and queries need to be rewritten.

I also like to use certain other server-specific functionality, such as query notification in SQL Server, which MySQL doesn't offer. So there, again, we use it when we can and don't worry about portability.

Furthermore, parts of our apps need to query schema information and act on it. Here, again, we have server-specific code for the different systems, instead of trying to restrict ourselves to the lowest common denominator.