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. 


Figure 1 – Product table with data

I renamed the column from NameOfProduct to ProductName


Figure 2 – Rename feature in Database Projects


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.


Figure 4 – Rename Column Script

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


Figure 5 – Product table data after the rename

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


More to explore