T-SQL "re-runnable" database update scripts - column deletion

Question

For our SQL Server database, we're using a versioning scheme to track schema updates. The idea is that you should be able to run this script to bring the schema from any previous version up to the current version. Running the master script again should only execute the newest schema updates.

The structure of the script is like this:

 SELECT @Installed = InstallDate FROM SystemSchemaVersion WHERE Major=1 AND Minor=0 AND Patch=0
 IF (@Installed IS NULL)
 BEGIN
    ...
    INSERT INTO SystemSchemaVersion (Major, Minor, Patch, InstallDate) VALUES (1, 0, 0, GetDate())
 END
 ELSE PRINT 'Version 1.0.0 was already installed on ' + Convert(varchar(10), @Installed)  

 SELECT @Installed = InstallDate FROM SystemSchemaVersion WHERE Major=1 AND Minor=0 AND Patch=1
 IF (@Installed IS NULL)
 BEGIN
    ...
    INSERT INTO SystemSchemaVersion (Major, Minor, Patch, InstallDate) VALUES (1, 0, 1, GetDate())
 END
 ELSE PRINT 'Version 1.0.1 was already installed on ' + Convert(varchar(10), @Installed)

This generally works very well. However, we've encountered a problem when a schema update DROPs a column that is included in a previous INSERT; that is, we have something like this:

 SELECT @Installed = InstallDate FROM SystemSchemaVersion WHERE Major=1 AND Minor=0 AND Patch=0
 IF (@Installed IS NULL)
 BEGIN
    INSERT [foo] ([a], [b], [OrganizationId]) VALUES (N'a', N'b', N'1');
    INSERT INTO SystemSchemaVersion (Major, Minor, Patch, InstallDate) VALUES (1, 0, 0, GetDate());
 END
 ELSE PRINT 'Version 1.0.0 was already installed on ' + Convert(varchar(10), @Installed)  

 SELECT @Installed = InstallDate FROM SystemSchemaVersion WHERE Major=1 AND Minor=0 AND Patch=1
 IF (@Installed IS NULL)
 BEGIN
    ALTER TABLE [foo] DROP COLUMN [OrganizationId];
    INSERT INTO SystemSchemaVersion (Major, Minor, Patch, InstallDate) VALUES (1, 0, 1, GetDate());
 END
 ELSE PRINT 'Version 1.0.1 was already installed on ' + Convert(varchar(10), @Installed)

This works fine the first time it is executed; version 1.0.1 is executed, and the column is dropped. However, running the script a second time yields:

    Msg 207, Level 16, State 1, Line 7118
    Invalid column name 'OrganizationId'.

That is, even though the INSERT inside the version 1.0.0 block isn't being executed, it's still being parsed and generating an invalid column error.

Any suggestions on how to work around this? Ideally I'd like to protect the INSERT with a conditional so that it's not even parsed, but that doesn't seem to be happening. I could dynamically perform the INSERTs inside sp_ExecuteSql() calls, but I'd prefer not to (would require a lot of retrofitting).

Thanks --

--Andy

Answer 1

Unfortunately this is similar to the problem you get when (inside a stored proc) you drop a temporary table and then recreate it. The parser will complain that it already exists, not seeming to realise that the temporary table has just been dropped.

If you separate it out with GO statements, then you should find the system will re-evaluate each section as it comes to it.

Rob

Answer 2

We use a NEARLY identical setup to deal with versioning the schema.

In general, your approach is totally sound. We've been running with this general setup for several years. Basically, to handle any destructive or non-compatible schema changes, we run the patches as a part of an automated CruiseControl.NET build.

So our database build looks like this...

• Restore from current PRODUCTION version backup.
• Check version of restored DB
• Run all patches (these are named by convention using major.minor.sql) that are later than the version indicated in the [Versions] table.

This way, we can rebuild all day long without any problems no matter what the patch does. This also ensures that when we do deploy to PRODUCTION there are no problems, since we've already deployed over the PRODUCTION db 1000x during development.

Answer 3

Have you tried dynamic Sql? Sadly, the parser will check the whole script before running it, so any invalid column will stop the execution.

Answer 4

Ok, I mis-read the question initially. :-)

If you change the insert lines from:

INSERT [foo] ([a], [b], [OrganizationId]) VALUES (N'a', N'b', N'1');

to:

exec('INSERT [foo] ([a], [b], [OrganizationId]) VALUES (''a'', ''b'', ''1'')');

You shouldn't have that problem, since the SQL "text" inside the exec won't be parsed until the exec() is actually called.

Ron

Answer 5

I like methodology for Jay Stevens