Sunday, March 27, 2016

Allow changes to SQL Server table schema if the changes require table re-creation

Modifying an SQL Server table that requires the table to be re-created is not allowed by default using SQL Server Management Studio for good reason. For example, changing a column of type nvarchar from 10 to 20 will result with the following error:

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.

To allow schema change for the above situation using SSMS, do the following:
  1. Open SSMS (SQL Server Management Studio). I am using SSMS 2014. 
  2. For good measure, back-up the database.
  3. Navigate to Tools | Options
  4. Click on Designers.
  5. Remove the check box on Prevent saving changes that require table re-creation
  6. Click on OK
  7. Proceed with changing the table schema.
  8. Again for good measure, enable Prevent saving changes that require table re-creation to prevent accidental table schema change that might drop some information.


No comments: