How Do DB Version Control Systems Lack in Script Semantics When Modeled on Source/Target Difference Comparisons

Question

There are several database version control solutions out there to assist with controlling not only the versioning of a database, but also deploying and synchronizing databases. These systems are typically based-off either a "source/target difference synchronization" model, or a "direct application of migration scripts" model. My question is around the source/target difference synchronization model.

In this model, you essentially take a snapshot of a source database (your basis of record) and run a comparison of this snapshot against a target database. The tool ideally will create the necessary scripts to synchronize the target database to have the same structure (and reference data) as the source database. One of the disadvantages of this model is that the tool might not be smart enough to capture the exact semantics of the database changes that were made, and will create "bad" scripts.

Are there any good examples of what a "bad" script is? I am failing to see why this is a disadvantage. For example, if I drop a column on a reference data table and then re-add it, I end up with a table with the same structure, but would have null (or default values) in the the re-added column. A synch tool might generate something like:

UPDATE table SET re_added_column = null where pk_column = <val_1>;
...
UPDATE table SET re_added_column = null where pk_column = <val_n>;

While the following might be more efficient (and represents exactly what happened):

ALTER table DROP COLUMN re_added_column;
ALTER table ADD COLUMN re_added_column;

In the example, however, I fail to see why this difference would be an actual cause of concern. Is it just that the former is "dirtier?" Are there any examples where there would be an ACTUAL problem with this behavior?

Answer 1

Dropping the column would remove all values from all rows of the table. If the change applies to all rows that could be in the database this might be acceptable. If the model tables are for a base set of records, it is possible the DROP COLUMN would drop data from other records.

The DDL (Data Definition Language):

ALTER table DROP COLUMN re_added_column;
ALTER table ADD COLUMN re_added_column;

Is somewhat equivalent to the the DML (Data Manipulation Language):

UPDATE table SET re_added_column = null;

However the DDL solution will move the column to the end of the row. This column position may be sub-optimal. The DDL solution may also require removal and recreation of Indices and Constraints.