Tips on writing SQL for multiple databases

Question

Different databases have differences in SQL support & implementation. Sometimes there is a difference in SQL syntax, sometimes support for some SQL commands is missing, sometimes the database has a feature that other databases do not have.

What are considered to be good practices in writing SQL queries that are good for different databases (MySQL, PostgreSQL, Oracle, MSSQL, SQLite) taking in account that the developer uses a framework (like CakePHP, Codeigniter, Zend etc.) that provides a database abstraction layer? What SQL syntax should the developer try to avoid?

Answer 1

Then you dig into using ORM, you'll find that for complex queries - it doesn't perform. It's hard enough for people to write SQL that performs well - I don't expect a DB abstraction layer fair any better. Most ORMs support native stored procedures... which defeats the purpose of using ORM.

ANSI SQL is striving to make SQL more portable amongst databases, but adoption varies from vendor to vendor. And ANSI syntax doesn't necessarily mean it performs as well as native syntax (IE: COALESCE vs native ISNULL/IFNULL/NVL/etc).

The reality is for getting the best performing database interaction, you need to write custom code for each vendor involved. Some would use this as a point to why the database should be nothing more than basic persistence because it's easier to maintain a central application. But this pales when you deal with high usage applications, who suffer because of multiple trips between the application and the database, poor data typing and table design. Frankly, it's a waste of a database...