Column Changes without Data Loss in Visual Studio 2010 Database Projects

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. 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.

I renamed the column from NameOfProduct to ProductName

‍

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.

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

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

Enjoy!