I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the current version of the schema, and I want to create a single script that upgrades the schema from any earlier version to the current version.
Something like this (pseudocode):
if (select v from version) = 1 create table newtable ... update version set v = 2 end if (select v from version) = 2 alter table newtable add column newcolumn ... update newtable set newcolumn = ... update version set v = 3 end if (select v from version) = 3 ... end ...
There are many problems here.
Mixing DDL and DML can lead to problems, like adding a new column and trying to update it in the same batch will cause an error that the column doesn't exist. So I thought that I should always separate them into different steps, and each step should be a different batch. Of course, every change step will end in a DML to change the version number, but that's okay. There should be transactions inside the DML batches, and let's not worry about the DDL batches.
I also want to make sure that during one execution, only one step is executed, because error handling could be a nightmare. Stopping the script after one step is also not trivial, neither in SSMS nor in SQLCMD. In my above sample, if we start at version 1, it will be updated to 2, then 3, then 4, etc. So I thought I would reverse the order of changes. First comes the change from 3 to 4, then from 2 to 3, then from 1 to 2, this way, only one step is executed at a time.
Does this look okay? Are there other things to consider?