Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created [duplicate]

Question

When I try to alter a data type of my table I get this horrible message from SQL Management Studion: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created". I already tried to do the modification by T-SQL and it worked, but why can't I just do this by design mode? I'm using SQL Server 2008 R2.

Answer 1

I would strongly suggest that you use T-SQL to make changes, or at the very least, preview the scripts that the Designers generate before committing them. However, if you want to do this in the designer, you can turn off that lock by going to Tools...Options...Designers..Table and Database Designers.. and unclick the "prevent saving changes that require table re-creation".

That lock is on by default for a reason; it keeps you from committing some change that is obfuscated by the designer.

EDIT: As noted in the comment below, you can't preview the changes unless you disable the lock. My point is that if you want to use the table-designer to work on a table with this feature disabled, you should be sure to always preview the changes before committing them. In short, options are:

• BEST PROCESS: Use T-SQL
• NOT GREAT: Disable the lock, use Table Designer, and ALWAYS preview changes
• CRAZY TALK: Click some buttons.