We have a 'Gender' column (that is a Boolean) storing 0's and 1's. All works well for a while.
- Figure: Anything wrong this Gender column?
Later you learn you need to change the data type to char(2) to support 'M', 'F', 'T', 'NA' and 'U'
- Figure: Caster Semenya has taught us a thing or two about the right data type for Gender
The data then must be migrated to the new data type this way:
- Rename 'Gender' to 'ztGender' *
- Add a new column 'Gender' with type char(2)
- Insert the existing data from 'ztGender' to 'Gender' (map 0 to 'F' and 1 to 'M')
- Delete the column ztGender*
*Note: zt stands for Temporary
- Figure: Changing the data type and data required a "Data Motion Script"
Visual Studio does not automatically support this scenario, as data type changes are not part of the refactoring tools. However, if you add pre and post scripting events to handle the data type change the rest of the changes are automatically handled for you.
- Figure: Don't use Data Dude
note: In order to achieve this you MUST use the built in Refactor tools as it create a log of all the refactors in order. This helps Visual Studio generate the schema compare and make sure no data is lost.
There are few options available to perform data type change correctly:
Use manual scripts. All data type changes including data migration can be performed by writing scripts manualy. This way you have full control over the change. It is recommended to use
DbUp to automate script deployment and keep track of all database changes.
Use Database Project. As mentioned above, Visual Studio does not support data type changes out of the box and should not be used to perform this kind of task.
- Use Entity Framework (EF) Code First Migrations. If your application uses Entity Framework Code First, then it is strongly recommended to use Migrations feature.
Using EF Code First Migrations is comparable to using one of the below combinations:
- DBUp +
DAC Support For SQL Server Objects and Versions (.dacpac files)
public partial class GenderToString : DbMigrationBad Example - the default scaffolded migration will not perform any mapping of your data
public override void Up()
AlterColumn("dbo.Customers", "Gender", c => c.String(maxLength: 2));
public override void Down()
AlterColumn("dbo.Customers", "Gender", c => c.Boolean(nullable: false));
public partial class GenderToString : DbMigrationGood Example - Data motion with EF Migrations
public override void Up()
AddColumn("dbo.Customers", "GenderTemp", c => c.Boolean(nullable: false));
Sql("UPDATE [dbo].[Customers] set GenderTemp = Gender");
AddColumn("dbo.Customers", "Gender", c => c.String(maxLength: 2));
Sql("UPDATE [dbo].[Customers] set Gender = 'M' where GenderTemp=1");
Sql("UPDATE [dbo].[Customers] set Gender = 'F' where GenderTemp=0");