Enable changes that require table re-creation

Standard

The Problem

When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.

Examples of changes that require table re-creation:

  • Change a column to no longer allow NULL values
  • Adding columns in the before another column
  • Moving a column

In earlier editions it was possible to allow some changes to be done directly using the GUI. What SSMS does in the background is unloading data of the table into a temporary table (not a # temp table), then deleting the original table and renaming the temporary table to the original table name.
You can imagine that actions like that would have a huge impact on a large (maybe production) table.

When you apply such a change you’ll get the following error message:

prevent changes error

The solution

Again, be very careful when enabling this option when you regularly work in production environments. I very much hope so that you never make these kind of changes using the GUI especially not in production. Just don’t!

To enable the changes in SSMS go to the menu “Tools” and click on “Options”.

Go to option “Designers” and uncheck the checkbox at “Prevent saving changes that require table re-creation”.

prevent_changes_solution

Click on “OK” and you’ll be able to apply the changes you couldn’t do before.

Remember that this option is set for your SSMS installation and that this will be used for every instance you connect to.

Happy developing!

Leave a Reply