Strategies for encapsulating the differences between SQL platforms?
===

I'm working on accelerating some slow operations here, and one case is a tree of parent-child relations in a table. Currently the system is running on SQLServer, and after some research I found that with common table expressions multiple queries can be compacted into one.

So far so good, but the syntax is specific to SQLServer and doing the same thing with (for example) Oracle would require a completely different syntax.

What can be done to structure the system to allow for future adaption to other RDBMS? My main concerns here are:

* SQL statements are littered over the code, and what may be possible with *one* statement in one RDBMS may require multiple statements in another, thus even the program logic may need changes.
* Even things that can be encapsulated nicely in one RDBMS (I'm using stored functions to hide some of the complexity in the DB) have subtle difference along different DB's or are not available at all.

So far I feel that everything that goes beyond the most simple SQL statements seems to always require vendor specific extensions, making it impossible to keep the differences hidden neatly in a few classes/stored SQL's (sure on *can* use a *framework* that has most of the abstraction already build in. But that also rules out many of the more useful features of the DB).

What strategies can be used to at least *ease* the pain of vendor differences? I'm aware that this is a very broad question that has no cure-all answer. But I'm hoping for some pointers and patterns to lessen the impact the DB has on the application.