As I read the guidance of many database professionals one concept appears to be frequently espoused. When writing SQL, avoid using engine specific syntax. Use ANSI standard syntax whenever possible, as supported by your database vendor. The reasoning is this:
If you use ANSI standard syntax, you can more easily host your application on a database from a different, compliant vendor.
I am completely in agreement with the argument. What I’m interested in understanding is a different question altogether. How often do you need to replace one database engine with a different engine?
Purchasing canned software I can say I am really pleased when the vendor supports my preferred engine. This allows me to utilize already existing licenses, infrastructure, and expertise. So, were I to be working on software to be sold commercially, it would make sense to be able to utilize mainstream data engines.
When it comes to in house database hosting, I have had very little change from one data engine to another. Sometimes this may occur during acquisition or merger, when consolidating resources. I have seen smaller departmental applications grow into enterprise tools, and thus require an upgraded data store. I’ve experienced a number of database upgrades from Paradox/Firebird, MS Access, and Watcom/SQL Anywhere to SQL Server or Oracle. I have some friends how moved from DB 2 to Oracle, etc.
If you think about what is common in most engines using ANSI standards fall pretty much in the range of most CRUD (Create/Retrieve/Update/Delete) syntax. That being said, unless you need the power of functions or stored procedures to optimize your database performance, then the ability to change data engines is probably less important, because you are most likely already covered.
The question then falls to what do you do when you need the extra power often found in implementations such as stored procedures? If you can’t use proprietary tools, what options are left to you? What do you think? Is this a conversation that is no longer relevant? Should new people entering the database field be considered with the commonalities and differences of the various data engines? Why not leave a comment and get into the conversation?
Cheers,
Ben