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.