Schema change table rebuild
I'm working on a script to keep table schemas synchronized.
Is there an exhaustive list of actions done to a table schema in MS SQL that requires the table to be dropped and recreated and the data to be reinserted?
You may be better off standardizing on the CREATE-COPY-DROP-RENAME (CCDR) strategy and only attempting an in-place alter in the few scenarios where your DDL will not require a rebuild rather than trying to compile the exhaustive list. This is the strategy described here: link.
AFAIK, you are only permitted to add columns to an existing table (without rebuilding) if the column is:
- added to the end of the table AND
- is nullable or has a default constraint
In all other cases, MSSQL will potentially fail if it does not know what to use as a value in the rows of the newly added column or data loss is a result (truncation for example). Even defaulted columns added in the middle will force a rebuild.
To further complicate things, in some cases the success of your deploy will depend on the type of data in the table, and not simply the schema involved. For example, altering a column length to a greater value (varchar(50) --> varchar(100)) will likely succeed; however, decreasing the length is only sometimes permitted. Migrating data type changes is another tricky mess.
In short, I would always rebuild and rarely alter in place.
To illustrate in-row data affecting outcome:
create table dbo.Yak(s varchar(100)); insert into dbo.Yak values(replicate('a', 100)); go -- attempt to alter datatype to 50 (FAIL: String or binary data would be truncated.) alter table dbo.Yak alter column s varchar(50); go -- shorten the data in row to avoid data loss delete from dbo.Yak; insert into dbo.Yak values(replicate('a', 50)); go -- again, attempt to alter datatype to 50 (SUCCESS) alter table dbo.Yak alter column s varchar(50); go select len(s),* from dbo.Yak; go --cleanup drop table dbo.Yak; go