Wednesday, February 29, 2012

Allowing Table Design Changes in SQL Server Management Studio

In SQL Server 2008 and above, there is a new setting which is configured by default which prevents any changes from being saved to the database that require dropping and re-creating the table.  Each time I install a new instance of SQL Server 2008 or SQL Server 2008 R2, I usually forget about changing this setting UNTIL the time I actually have to make a design change and receive the error message while attempting to make my change.

If you have encountered this error in the past, you probably have already had to solve this problem.  However, if you are relatively new to SQL Server, you may not yet know about this setting.

Below are the steps to allow these Design changes:

  1. From within SQL Server Management Studio, open up the Tools menu
  2. Click on the Options menu item
  3. Click on the + icon to expand the Designers section in the left hand pane
  4. You should see an option in the right hand pane that states "Prevent saving changes that require table re-creation" that is already checked by default.
  5. Uncheck the checkbox and click the OK button.
  6. You should now be able to apply any changes that you need in Design view!

No comments:

Post a Comment