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?

Answers


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:

  1. added to the end of the table AND
  2. 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

Need Your Help

Decryption is not working using RSA

java encryption

I am encryting a message at client side and decrypting it at server side while decrypting it goes in infinite loop and terminate. Can you guys tell me what's wrong in code.Encryption is working fin...

Passing sql file name from a jsp to a servlet for data import

java mysql html5 jsp servlets

I am retrieving a database by passing an sql source file as following.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.