Altering a Table – “Saving Changes is Not Permitted”

So you may or may not know this. SQL Server 2008 and up have a restrictive setting for altering tables (to prevent users from shooting themselves in the foot). To get around this message, go to Tools>Options>Designers; then un-check “Prevent saving changes that require table re-creation”.

That is well and good but has lead me to ask the question, what changes will require table re-creation? Here is what I found:

  • Changing column sizes.
  • Setting a column to allow nulls or to not allow nulls.
  • Changing a column type to a type that does not convert well. Int to bigint is fine but nvarchar to text requires table re-creation.
  • Changing foreign key constraints.
  • Modifying identity properties of columns.

Here is something that you can do though:

  • Add  a column and it’s properties.
  • Delete a column, kind of strange since this would be destroying data. What gives Microsoft?
Advertisements