How to create a versioning DDL/DML change script for SQL Server? 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?
This is just my 2 cents worth...

The way the company I work for currently does their changes is by keeping each script separate and having every script re-runnable, therefore if you notice an issue with a piece of code, there will not be a problem altering it and re-running the whole script.

So we wrap every DDL statement inside a check to see if the change has already been made:

IF OBJECT_ID(N'dbo.my_table', 'U') IS NULL
BEGIN
    CREATE TABLE.....
END


Otherwise, with the way you've currently got the setup, if you make a mistake in version 2 and accidentally omit a key DDL statement, you cannot just add it to that version and re-run the entire v2 script. Firstly because the version number would have updated to 3, then, even if you alter the version number back to 2, the following line would throw an error as the column already exists:

alter table newtable add column newcolumn ...