SQL Server 2008 management studio drop tables warning?

Question

Why does SQL server 2008 always warn about needing to drop tables to rename columns? I was under impression this was not required unless it was a key. If I change a column from null to not null even if is not a key field it wants to drop table and all relations.

Answer 1

If you're editing the table in the designer then it's the designer that is being pedantic. Try changing (unchecking) these options:

Tools > Designers > Prevent saving changes that require table re-creation

The designer still throws an warning dialog after unchecking that but there is also this option to uncheck:

Tools > Designers > Warn about tables affected

I believe the reason the designer has to drop tables to rename columns is because there is no SQL command to do that, and what it does instead is copy the table's data into a temp table, drop the table, create a new table with the altered column name and copy the data into it.