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?