Database Interoperability
Database Interoperability
By : Kasim Wirama, MCDBA
You need to consider your SQL syntax among database platform accessed from your application such as .NET, Java, etc. Interoperability in database is some of database issue that you need to consider and it seems that it is not easy to make your SQL code is portable among database platform.
I refer to these database platforms because they are widely used in database market. They are SQL Server, Oracle, DB2, and MySQL. I can suggest that the more you stick to ANSI standard, the more portable your SQL code, at least you doesn’t need much effort by doing little changes.
Here are some aspects as guidance to make your database more portable:
1. Avoid these data types : IMAGE, SQL_VARIANT, TABLE, VARCHAR(max) and TEXT when you stick with ANSI standard.
2. Avoid quoted, bracketed identifier.
3. Use ANSI only for INSERT, UPDATE, and DELETE.
4. Avoid using hints (beginning with WITH keyword) because they are specific to each database platforms.
5. Avoid using INSERT…. EXECUTE, because only SQL Server has this handy feature. I love this construct but for interoperability this should be sacrificed.
6. Avoid these SELECT constructs in SQL Server.
SELECT….INTO…FROM
WITH CUBE/ROLLUP
COMPUTE
FOR XML
7. If you interoperate your SQL Code between SQL and Oracle you can partition your table/index, otherwise avoid partitioning.
Other aspects that you can’t avoid to make effort on SQL interoperability is that database functions and stored procedure because ANSI just approaches procedural extensions in ANSI-2003, not specific extensions.