The Myth of SQL Code Portability?

While I have never had the "pleasure" of working on a project that required I write ANSI standard SQL so the code for portability, I have worked with products that have been written that way. Alexander Kuznetsov has an interesting blog post, Writing ANSI Standard SQL is not practical, where he addresses the issue from his experience. He notes that best practices dictate using stored procedures for data access and the big difference between SQL Server and Oracle implementation. He also links to another article he wrote on DevX, Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again., where he explains some situations where SQL Server and Oracle will return different results from the same data. Sure some of these issues are somewhat contrived, but they do have real world implications. Overall I would say that I agree with his conclusions.

I would also like to add that, in my experience with applications where the SQL was written for portability, I have seen issues with performance. This is usually because the SQL does not take advantage of some of the T-SQL extensions or uses a less than optimal (for SQL Server) query syntax. It also makes security harder because it does not use stored procedures so you have to enable direct access to base tables. One product I worked with that was written with portability would not implement Primary and Foreign Keys.

I understand trying to maximize your the profitability of your code, but with performance being such a big issue now, I don't see how you can not write platform specific code. The other issue is that these vendors often do not have platform experts, whether SQL Server or Oracle, so when you approach them with an issue they cannot offer proper help.

One last link to a post by Brad McGahee, Challenging the Tyranny of Third-Party Vendors: A DBA’s Manifesto