0

Let's say I have a table like so:

CREATE TABLE Foo
(
    Id INT IDENTITY NOT NULL PRIMARY KEY,
    Data VARCHAR(10) NOT NULL,
    TimeStamp DATETIME NOT NULL DEFAULT GETUTCDATE()
);

Now let's say I build this in a SQL Server Database Project, and I publish this in version 1.0 of my application. The application is deployed, and the table is used as expected.

For the 1.1 release, the product owners decide they want to track the source of the data, and this will be a required column going forward. For the data that already exists in the database, if the Data column is numeric, they want the Source to be 'NUMBER'. If not, it should be 'UNKNOWN'.

The table in the the database project now looks like this:

CREATE TABLE Foo
(
    Id INT IDENTITY NOT NULL PRIMARY KEY,
    Data VARCHAR(10) NOT NULL,
    Source VARCHAR(10) NOT NULL,
    TimeStamp DATETIME NOT NULL DEFAULT GETUTCDATE(),
);

This builds fine, but deploying an upgrade would be a problem. This would break if data exists in the table. The generated script will create a temporary table, move data from the old table into the temp one, drop the old table, and rename the temp table to the original name... but it won't if there's data in that table, because it would fail to assign values to the non-nullable column Source.

For trivial refactors, the refactor log tracks changes in the schema, and maintains awareness of the modified database objects, but there doesn't seem to be a way to do this when you get your hands a little dirty.

How can the Database Project be leveraged to replace the default script for this change with a custom one that properly captures the upgrade logic? There must be some way to address this issue.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
  • For starters, I wouldn't add a column in the middle of the other columns. Add it at the end. If column order matters to the users, mask it in a view or somewhere else in the code. Otherwise, this seems to be working as designed - there's no way to add a column in the middle of the table without rebuilding it. You probably want to turn off the check for data loss - I think that might be interfering with the schema change in this particular case. – Peter Schott Mar 19 '15 at 12:25
  • @PeterSchott you're missing the point of the question. Rebuilding it is fine; the sql is properly generated to make that happen. However, losing data would naturally be acceptable. The important, non-covered part of this refactor is the logic necessary to backfill data on a column that did not previously exist. – Jeremy Holovacs Mar 19 '15 at 16:09
  • * I meant naturally be _un_acceptable... – Jeremy Holovacs Mar 19 '15 at 17:29
  • You mean like creating the column with a (preferably named) default and updating it to be correct in a post-deploy script? As it stands, that script would fail when trying to repopulate the table because the column isn't NULLable or doesn't have a default. – Peter Schott Mar 19 '15 at 20:11
  • @PeterSchott yes, that is what this question is about. This becomes a more complex operation than the script generator will support, and I don't see a way to override the automated script with a custom script to fix it... but if MS expects database projects to be used to not just create databases but maintain them, there must be a way. – Jeremy Holovacs Mar 19 '15 at 21:45
  • When it comes down to something like that, we've typically done a couple of releases - not ideal, but it can do the iterative part. One limitation w/ SSDT is that you really need to be able to program if you want to inject some custom step into the script. Red-Gate's SQL Source Control apparently has some capability to handle custom scripts as part of the release. --- I've never actually had a need to do massive refactoring as part of a release so it hasn't been a huge problem for us, but doing it iteratively seems safer in the long run. – Peter Schott Mar 20 '15 at 14:29

0 Answers0