Set SQL Server Database to only allow ANSI Compliant SQL Code

Question

Is there a way that I can force my sql server database to only use SQL Compliant code?

This way the SQL Code is easy transportable to other Database systems like Oracle, IBM and other ANSI Compliant systems.

Or other viable approach to get the same result?

Answer 1

Nope, not really.

However, it doesn't really make much of a difference - the idea that you could use the same (ANSI or otherwise) SQL accross widely different SQL engines is just an illusion. The usual approach nowadays is to use someone who builds the queries for you - at least that allows some optimizations before sending the SQL off. You can even have different database structures if that's important.

Even the data types and their representations may differ between different database clients. Right now, for example, I'm working on a legacy Oracle application which uses DataSets. On Oracle, all numbers are decimals in C#. On MS SQL, some are decimals, some are ints. Even using purely ANSI SQL, you need to handle tiny incompatibilities like this, and it's very hard to chase performance as well.

For example, some Oracle queries in the legacy application use explicit indices - it really is necessary thanks to the (bad) way the queries are built and formed, but only on Oracle - MS SQL doesn't seem to require them at all. There's SQLs that are better taken care of by the MS SQL execution planner than the Oracle one and vice versa - some of the performance critical queries look completely different on the two engines. And it would help performance a lot if I could have different DB structures for each of those. And there's different paradigms of development as well - Oracle code requests IDs prior to inserts (etc.), while the standard on MS SQL is to let the DB engine handle that - you just make sure the associations on code side are properly translated over to the relational fields on the DB side.

And that's just two major DB engines. MySQL, Postgres, DB2... all of them have tiny quirks, even in full-ANSI mode. Add to that the fact that ANSI standards aren't even fully implemented in any of those, and the bubble bursts easily.

Even using an intermediate (like some O/RM system) will expose lots of leaks. The key is to keep as far away from anything specific as long as you can - and make sure everyone is well aware of the places where you can't keep up with the charade.