Is there a way that I can force my sql server database to only use SQL Compliant code?
是否有一种方法可以强制我的sql server数据库只使用符合sql的代码?
This way the SQL Code is easy transportable to other Database systems like Oracle, IBM and other ANSI Compliant systems.
这样，SQL代码就可以轻松地传输到其他数据库系统，如Oracle、IBM和其他ANSI兼容系统。
Or other viable approach to get the same result?
或者其他可行的方法得到同样的结果?
1 个解决方案
#1
4
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.
然而，它实际上并没有多大的区别——您可以使用相同(ANSI或其他)的不同SQL引擎的想法只是一种幻觉。现在通常的方法是使用为您构建查询的人——至少在发送SQL之前允许进行一些优化。
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.
甚至不同的数据库客户机之间的数据类型和它们的表示也可能不同。现在，例如，我正在处理一个使用数据集的遗留Oracle应用程序。在Oracle上，所有数字在c#中都是小数。在MS SQL上，有些是小数，有些是ints。即使使用纯ANSI SQL，您也需要处理类似这样的微小不兼容问题，而且要追求性能也是非常困难的。
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.
例如，遗留应用程序中的一些Oracle查询使用显式索引——由于构建和形成查询的方式(很糟糕)，这确实是必要的，但仅在Oracle上——MS SQL似乎根本不需要它们。SQL执行计划员比Oracle的SQL执行计划更好地处理SQLs，反之亦然——一些性能关键的查询在两个引擎上看起来完全不同。如果我有不同的DB结构，这对性能会有很大帮助。有不同的发展模式——甲骨文代码请求id之前插入(等等),而MS SQL标准是让DB引擎处理——你只需确保代码一边正确翻译的关联到关系字段在数据库方面。
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.
这是两个主要的DB引擎。MySQL、Postgres,DB2……他们都有小怪癖，即使是全ansi模式。此外，ANSI标准甚至没有在这些标准中得到充分实施，因此泡沫很容易破裂。
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.
即使使用中间产品(如某些O/RM系统)也会暴露大量的泄漏。关键是要尽可能远离任何特定的东西——并且确保每个人都很清楚哪些地方是你无法跟上的。
#1
4
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.
然而，它实际上并没有多大的区别——您可以使用相同(ANSI或其他)的不同SQL引擎的想法只是一种幻觉。现在通常的方法是使用为您构建查询的人——至少在发送SQL之前允许进行一些优化。
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.
甚至不同的数据库客户机之间的数据类型和它们的表示也可能不同。现在，例如，我正在处理一个使用数据集的遗留Oracle应用程序。在Oracle上，所有数字在c#中都是小数。在MS SQL上，有些是小数，有些是ints。即使使用纯ANSI SQL，您也需要处理类似这样的微小不兼容问题，而且要追求性能也是非常困难的。
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.
例如，遗留应用程序中的一些Oracle查询使用显式索引——由于构建和形成查询的方式(很糟糕)，这确实是必要的，但仅在Oracle上——MS SQL似乎根本不需要它们。SQL执行计划员比Oracle的SQL执行计划更好地处理SQLs，反之亦然——一些性能关键的查询在两个引擎上看起来完全不同。如果我有不同的DB结构，这对性能会有很大帮助。有不同的发展模式——甲骨文代码请求id之前插入(等等),而MS SQL标准是让DB引擎处理——你只需确保代码一边正确翻译的关联到关系字段在数据库方面。
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.
这是两个主要的DB引擎。MySQL、Postgres,DB2……他们都有小怪癖，即使是全ansi模式。此外，ANSI标准甚至没有在这些标准中得到充分实施，因此泡沫很容易破裂。
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.
即使使用中间产品(如某些O/RM系统)也会暴露大量的泄漏。关键是要尽可能远离任何特定的东西——并且确保每个人都很清楚哪些地方是你无法跟上的。