One of the most tedious tasks in database-driven software development is to make database change scripts. You meet it just as you product version becomes higher than 1. You usually have to maintain create script that creates you DB from scratch and also change script(s) that will upgrade existing databases of previous version(s) to the latest one. And those databases most probably have customer data in them that you cannot damage.
In the latest release of Visual Studio there is new type of database projects that helps maintaining your database scripts significantly. I've already wrote review of new features, but such important topic as generation of change scripts in missing in that post, so I decided to write new post on it.
From the first sight it seems that VS can generate only create scripts, which it places in /sql folder by default. Hopefully, it also can generate change script, although this feature not so easy to discover. It can be found in database compare tool. It generates scripts based on differences between two databases (between schema that you have in DB project and some DB instance). It is not well automated, so you have to copy the script it generates, review it manually, and save in a file yourself.
Some manual changes may be need in script. One good example: if you have used rename wizard to rename column in some table you may expect that data that was in that column will be kept by a script. Unfortunately, this type of change is not handled well by VS 2008. Yes, there is warning in UI, but who reads those warnings :-)
There is option in project properties "Perform 'smart' column name matching when you add or rename a column". Here is what MSDN says about this option:
Specifies whether to apply a heuristic when you deploy updates to determine when to rename a column instead of performing a DROP and an ADD operation. The heuristic is based on the properties of the column and the names of the source and target columns.
I hoped that setting this option will fix the problem with column rename, but unfortunately this did not work for me. I tried renaming CategoryName -> NewCategoryName and CategoryName -> Category1. In both cases this did not work. Perhaps they need to provide more info on the heuristic they use...
There is also protection from data loss. You can control it by setting in project properties. Unfortunately, changing this setting did not work for me too.
Anyway, this is undoubtedly a great feature. And should help maintaining incremental update scripts a lot.