The temptation to use vendor-specific extensions to SQL should be avoided at all costs. Advocate for use of ANSI-standard SQL, and not proprietary extensions, in access to relational database stores. This concept is applicable to other forms of databases also. For example, an XML database should be exclusively accessed using XPath or XQuery.
By utilizing a separate data access tier, data architecture can minimize the impact of calling applications since the APIs used by higher tiers do not change. The implementation of stored procedures is another form of proprietary access to a database that should be avoided. Stored procedures are specific to the database implementation in use and are difficult to migrate if a different implementation of a database is required.
Those familiar with Java and J2EE will see this principle represented by Enterprise Java Beans and will notice the preference of the Java community to use container-managed persistence, which does not support the use of stored procedures. The Java community is slowly adopting APIs such as the Java Data Object (JDO) specification that allows for data store independence.
Use of database triggers is also a proprietary extension of a database. It is a good practice to use database triggers for the sole purpose of supporting referential integrity and no more. |