Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 2, 2013

Database Projects and Refactoring

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:

image

In a later iteration, a feature is implemented to maintain customer types, so we need to change the CustomerType field to a CustomerTypeId.

image

Seems easy enough. But click the Update button and you get this:

image

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];

GO
ALTER TABLE [dbo].[Customer]
    ADD [CustomerTypeId] INT NULL;

So if the data looks like this before you run the update script:

image

After, it looks like this:

image

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:

image

Visual Studio then creates a refactor file:

image

When you publish the database, the DACPAC includes this refactor file. So the generated script to update the production database looks like this:

GO
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’;

GO
EXECUTE sp_rename @objname = N'[dbo].[Customer].[CustomerType]’, @newname = N’CustomerTypeId’, @objtype = N’COLUMN’;
GO

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.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

2 Comments

  1.   Dan — April 26, 2016 @ 7:23 pm    Reply

    This only resolves RENAME, how about changing the datatype from Int to Decimal, or extending a decimal value with precision from 3,2 to 8,2 ?

    •   Corey — November 11, 2016 @ 3:20 pm    Reply

      I have a similar question. Is there a way to change data types across multiple objects without opening them one at a time. Change all INT to BIGINT, across all tables in your project?

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs