24

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.

BWS
  • 3,786
  • 18
  • 25
wmrodrigues
  • 483
  • 1
  • 3
  • 10
  • 1
    There is an answer here: http://stackoverflow.com/questions/6810425/saving-changes-is-not-permitted-in-sql-server – Giacomo Degli Esposti Nov 21 '13 at 14:31
  • Changes to table structure should be scripted out and saved in source control like all other code. Never use the designer for this. Alter table does not require the table to be dropped and recreated, the designer does. – HLGEM Nov 21 '13 at 16:32

3 Answers3

66

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.
Stuart Ainsworth
  • 12,792
  • 41
  • 46
  • 2
    Could you add how you're supposed to "preview the scripts the Designers generate"? Right-clicking and Generate Scripts tries to save again. What's the point? – makhdumi Feb 02 '16 at 21:53
28

To change the Prevent saving changes that require the table re-creation option, follow these steps: Open SQL Server Management Studio (SSMS). On the Tools menu, click Options. In the navigation pane of the Options window, click Designers. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

user3646967
  • 281
  • 3
  • 2
1

Just to add to what Stuart Ainsworth said, you can right-click in the SSMS designer window and select the 'Generate Change Script...' option after you have edited to generate and review the script created by the designer for your changes.

ThePG
  • 41
  • 2