How different are the top DB vendor’s SQL (DML-) dialects from each other

dmlfeature-comparisonsql-standard

Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from each other. Since I come from an Oracle background, I am especially interested in

analytical functions
model clauses
hierachical queries (start with .. connect by comes to mind)
any other feature that is more than the usual (select x, y from a, b where...)

Probably, the question boils down to if and to what extent these features are regulated by an ansi standard.

Practically, I'd like to know if there are "rule of thumbs" that would indicate if (and how) I can take an SQL DML-statement that runs on one database and let it run on an other database.

Best Answer

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional functionality beyond the standards.

From w3schools:

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

The differences between the SQL dialects is too great in the more specialized areas you listed to simplify the problem down to a “rule of thumb”. In these situations you probably should convert the entire statement for each database. By doing this each statement can be optimized for each platform taking advantage of the special features each database can provide. Writing SQL to lowest common denominator (ANSI) may simplify migration, but at the cost of performance.

SQL Dialects Reference gives some useful comparisons of the SQL variants. For more information I recommend searching for the specific dialects you are switching between like this:

differences between mysql and oracle sql

Related Solutions

SQL Server – How to Create a Versioning DDL/DML Change Script

I did something very similar, but not as a single script. I used migrations. I also described this in Version Control and your Database. And indeed, every step is a different script, and the application drives the upgrade (the migration). Every step is tested. Every change is an upgrade, be it DDL or DML, it matters not. There are DML changes like changing some catalog entries or application lookup tables, these are upgrades. Of course, I'm not talking about DML for the actual application content.

Mixing DML and DDL should not cause problems. Scripts can contain multiple batches. Mixing DML and DDL inside a transaction can lead to problem though, and should be avoided.

Do not try to make the scripts idempotent (safe to run twice). That is going to add a tonne of problems like check if table exists etc. Do not try to make the scripts ACID (either all upgrade succeeds, or nothing); it is impossible. In case of error, revert to a backup. If the cost of restore is prohibitive (huge DB) then you should have a good battery of tests.

Test the scripts with significant size DBs so you don't run into size-of-data operation surprises in production upgrade.

Oh, and if these erorrlog messages look familiar:

Converting database 'x' from version 611 to the current version 655. 
Database 'x' running the upgrade step from version 611 to version 621. 
Database 'x' running the upgrade step from version 621 to version 622. 
Database 'x' running the upgrade step from version 622 to version 625.

that is because this is how the SQL Server itself takes care of schema versioning.

SQL Server 2008 R2 – How to Audit All DML Operations

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days.

As suggested by Kin, have a look at Nacho's FAQ. Basically:

The DMV can reset if:
- the server is restarted
- an index is dropped and re-created
The DMV can be empty if you start with -x, -m, or trace flag 2330
If you have a filtered index, updates will increment the usage stats even if the update did not affect the rows in the index
If any column in the clustering key is modified, all non-clustered indexes also increment their usage stats
Errors occurring after a DML attempt is made will still increment counters
Index usage tells you number of operations - it does not have any way to report to you number of rows or pages touched (an update to every row in a very wide table looks the same as an update to a single row in a very skinny table) - if you want more details on actual volume touched, see sys.dm_db_index_operational_stats.

And also Tim Ford's series on snapshotting the DMV:

Retaining historical index usage statistics for SQL Server : Part 1 | Part 2 | Part 3

Best Answer

Related Solutions

SQL Server – How to Create a Versioning DDL/DML Change Script

SQL Server 2008 R2 – How to Audit All DML Operations

Related Question