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?