SQL Language Extensions

The most significant migration issue that confronts the developer is the implementation of the SQL-92 SQL language standard and the extensions that each RDBMS has to offer. Some developers use only standard SQL language statements, preferring to keep their program code as generic as possible. Generally, this means restricting program code to the entry-level SQL-92 standard, which is implemented consistently across many database products, including Oracle and SQL Server.

This approach can produce unneeded complexity in the program code and can substantially affect program performance. For example, Oracle’s DECODE function is a nonstandard SQL extension specific to Oracle. Microsoft SQL Server’s CASE expression is a SQL-92 extension beyond entry level and is not implemented in all database products.

Both the Oracle DECODE and the SQL Server CASE expressions can perform sophisticated conditional evaluation from within a query. The alternative to not using these functions is to perform the function programmatically, which might require that substantially more data be retrieved from the RDBMS.

Also, procedural extensions to the SQL language can cause difficulties. The Oracle PL/SQL and SQL Server Transact-SQL languages are similar in function, but different in syntax. There is no exact symmetry between each RDBMS and its procedural extensions. Consequently, you might decide not to use stored programs such as procedures and triggers. This is unfortunate because they can offer substantial performance and security benefits that cannot be duplicated in any other way.

The use of proprietary development interfaces introduces additional issues. The conversion of a program using the Oracle OCI (Oracle Call Interface) often requires a significant investment in resources. When developing an application that may use multiple RDBMSs, consider using the Open Database Connectivity (ODBC) interface.