I attended an IBM briefing on analytic functions in version 11 of DB2 last week. Part of the presentation consisted of showing TBC analytic benchmarks, which are some pretty complicated and ugly things. There was a sweet a suite of 99 programs which all of the products have been run against. What was interesting to me was how the TPC Council ranks the products and the queries. Imagine a grid with the identifier of each query, colored green (runs immediately as written), red (cannot be run at all), and two shades of orange (could be rewritten in dialect with minor effort, or could be rewritten in dialect with major effort).
Not too surprisingly, since it is a vendor presentation, the new version of DB2 was all green. But in fairness, DB2 has really, really good ANSI/ISO conformance. Some of the other products like SQL Server, Oracle, etc. had red cells in the grid and various amounts of orange.
Now I assume the queries were provided by each of these various product vendors and submitted to the TPC. What it showed was that you can write ANSI/ISO standard SQL in all the major SQL products (and some of the minor ones), either directly or with some minor effort.
What was interesting was that the queries written to standard (the green guys) had better performance numbers than the orange. Of course the red guys had no performance at all, but that is another issue.
The conclusion I came to was to was (1) you really can write ANSI/ISO standard SQL in virtually every product on the market with little or no effort (2) avoiding highly proprietary features actually has a pay off when you start doing large amounts of data. Vendors tend to spend their efforts on improving core standard features, and not adding bells and whistles to proprietary features. (3) since one vendor shops no longer exist in the real world (it is not like the days when we had IBM and the BUNCH), portable simple code makes moving data around your your company much, much easier.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL