Database Projects and Refactoring
Fri, Aug 2 2013 9:56
There are often times that you need to update a database schema. But if that database is already in production, some changes, such as simply changing a column name, are difficult because of the potential data loss.
For an introduction to Database Projects in Visual Studio 2012, please see this post.
Let's look at an example.
The Customer table is defined as follows:
In a later iteration, a feature is implemented to maintain customer types, so we need to change the CustomerType field to a CustomerTypeId.
Seems easy enough. But click the Update button and you get this:
The column is just being renamed, why would data loss occur?
The old SQL Server tools didn't understand the concept of just changing a column name. Rather, they saw the change as a DROP and ADD. If you click Generate Script on the above dialog, you can see the DROP and ADD in the script:
ALTER TABLE [dbo].[Customer] DROP COLUMN [CustomerType];
ALTER TABLE [dbo].[Customer]
ADD [CustomerTypeId] INT NULL;
So if the data looks like this before you run the update script:
After, it looks like this:
Bummer! That won't due when updating the production database!
The latest SQL Server Data Tools includes the ability to refactor the database, including changing a column name, without data loss.
Instead of updating the database directly, edit the column name using the Database project table script as shown below:
Visual Studio then creates a refactor file:
When you publish the database, the DACPAC includes this refactor file. So the generated script to update the production database looks like this:
PRINT N'The following operation was generated from a refactoring log file 07117693-7a12-450c-b0a0-bc5ad854d94e';
PRINT N'Rename [dbo].[Customer].[CustomerType] to CustomerTypeId';
EXECUTE sp_rename @objname = N'[dbo].[Customer].[CustomerType]', @newname = N'CustomerTypeId', @objtype = N'COLUMN';
No data loss!
Use the refactoring features of the Database Project in Visual Studio 2012 any time you need to rename a column or perform other schema changes that could result in a data loss.
For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.