Database Abstraction - It's Stored Procedures
There are two situations that get discussed reasonably often relating to databases and applications. The first is the possibility that a database engine (the RDBMs) might be changed at some point from let's say, SQL Server to Oracle. In my career in the database-web application, which spans 16 years, I have literally never seen that happen with any database of any palpable size. I have seen several occasions where Access was up sized to SQL Server but I see that as different. The second situation, which I have seen several times, is where a new application is created to utilize an RDBMs that already existed or an existing application is extended to use an existing RDBMs. This second situation, which is discussed far less often, as it pertains to database abstraction is actually a far more real and occurring situation. In either of these two situations, efficient abstraction of the RDBMs from the application code makes the operation easier to accomplish.
In my experience, there is one method of abstraction that surpasses all others in terms of continuity of standards and usability across all application code development ASP, ASPX, CFML, JSP .NET PHP etc etc. That abstraction layer is Stored Procedures not some sort of code-level paradigm. My advise is used Stored Procedures wherever possible, avoid in-line SQL and also any paradigm that is application code dependent when interacting with RDBM's. Ongoing, this will make your development processes much smoother and predictable.
This blog piece of mine was inspired by one created by the author of the SQL Server 2005 Bible - Paul Nielsen
Just a reminder that I will be in Boston/Cambridge on May 11 and 12 at The Charles Hotel, in the bar from 7:00PM on if you want to come down to chat about all things Enterprise and HA.