Phil Factor (5/6/2010)
anything that involves a table schema change for example kind of makes the whole approach a bit complex and prone to problems
Right on the nail. If you can crack the problem of source control for tables, then you're probably home and dry. This is a problem that is ridiculously hard. Tables don't, and most certainly can't, have a source of the same type as a C# source, or a stored procedure. You can't compare tables merely from scripts. I can generate the same table using scripts that are really quite different in the way that they represent the table and its associated components, and I can reverse-engineer a table-build script in a number of different ways. The only effective way to detect a difference between two databases is to compare the metadata. Tables have a whole lot of dependent columns, indexes, constraints and triggers. How do you relate this lot together in the source-control model? Is a table, together with all its dependent components, one single object? If so, how come you can change it without requiring to check out, or 'reverse-engineer', the 'source'? How do you deal with something as potentially complex as a trigger or check constraint, which surely needs a separate identity in source control? (..and so on.. and so on ...) So often, folks move over from traditional programming and wonder why source-control is all so different with SQL Databases. It can be done, but it takes a great deal of head-scratching to get it right.
Yes that's one of the tougher areas of deployment of changes. We have a sequentially numbered set of 'incremental' scripts for DDL changes. All scripts are rerunable so for example if a new column is to be added they check for existance before trying to add the column, this means that script scn be rerun without causing errors e.g.:
IF NOT EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
AND COLUMN_NAME = 'mycol1')
BEGIN
ALTER TABLE dbo.myTable
ADD mycol1 int NULL
...
This means that if the incremental files are all run in the right order the correct changes to the database should be made.