Column Changes without Data Loss in Visual Studio 2010 Database Projects

Share on facebook
Share on twitter
Share on linkedin

Last week I had the opportunity to speak at Omaha’s first SQL Saturday.  My talk was on Continuous Integration with SQL Server Databases.  I had a good turnout and some great questions at my session.  Here are the slides from my talk.  I mistakenly mentioned in the talk that column changes would be treated as a Drop and an Add, thus resulting in data loss.  Visual Studio 2010 Database Projects track the changes like this and incorporates the column change into the delta script. 

In my example, I have a Product table with existing data. 

image_thumb_42.png

Figure 1 – Product table with data

I renamed the column from NameOfProduct to ProductName

image_thumb_43.png

Figure 2 – Rename feature in Database Projects

image_thumb_44.png

Figure 3 – Preview Changes Dialog

You can see that the delta script that was generated by the Deploy option in the Visual Studio Database Project is aware of the column name change.  The script calls the sp_rename stored procedure to rename the column name and keep the data intact.

image_thumb_45.png

Figure 4 – Rename Column Script

Here are the results of table after the rename. No data loss!

image_thumb_46.png

Figure 5 – Product table data after the rename

In my next post I’ll discuss specifics around the CI for SQL Server databases solution.

Enjoy!

More to explore