Can No Longer Change Tables in SQL Server 2008 Management Studio

Have you made a design change to a table in SQL Server 2008 and got an error message saying that "Saving changes is not permitted? The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."



The default option has been changed in SQL 2008, so that changes requiring dropping and recreating tables are not permitted dynamically. I guess this has been changed because too many DBAs have made changes to large tables and got themselves into trouble.


If you know that your table is small, and you want to be able to save changes, you can simply change the default action in the Tools / Options / Designers settings and uncheck the "Prevent saving changes that require table re-creation" option.

Warning, don't use this to make changes to very large tables. For very large tables, I suggest you manually create the new table and use SSIS to load the new table from the old table. Then drop the old table and rename the new table. Also remember to recompile your views on this table.



http://RichardLees.com.au