Alter SQL Table Containing Data from Design View

We will try to get rid of below Error in this Article. "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 to option Prevent saving changes that require the table to be re-created."


This article will help the Beginner and Basic Users of Microsoft SQL. As we all know, all kind of Application developed with .NET, whether it is Windows based or Web based use databases and in most cases Microsoft SQL. The reason behind this might be simply easier to learn and simple Queries. Also as being Microsoft Product, it is also included with .NET courses with the name of ADO.NET.

Microsoft SQL is a very vast and complicated RDBMS application, but Microsoft Provides an Applications with simple Graphical interface named Microsoft SQL Server Management Studio (in short SSMS), which helps performing all basic tasks like Creating Database, Adding Tables, Running Queries, Viewing Results, Adding Data, Altering Data, etc and other various management tasks. And Best of its feature is, it saves a lot of Time. You need not to write Complicated Query each time.


But enhancing security, it creates some problems with Default Setting.

Today we will change setting of SSMS to solve one of most common problem.

You must have faced a problem. I wont describe it by words, instead I will show you what it use to Look like.
Error Screen - Table Re-Creations

Now, remember. How many times does this Dialogue Annoyed you and created Problem. The above snapshot is captures which I tried to Add a Row in Existing Table that has Relations and some data.

Basically Error is simple and Logical. It is telling that, you have Table, that has Data and if you want to change the Structure of Table, you have to DROP Table and CREATE new one. However, we don't want to Loose our data and no complications.


Now without wasting our time, lets move to Process.
  • 1. Click on Tools Menu on SSMS and choose Options. Following Windows will open.
    Options SSMS

  • 2. Expand Designers Node and choose Table and Database Designers.
    Designer Options SSMS

  • 3. Now un-check the Options, Prevent Saving Changes that require table re-creation. And Click OK.
    Designers - Table & Database - Options - SSMS

Now, you are back to SSMS and try to Save your changes by Just Pressing Ctrl+S. You will see following screen.
Solved - SSMS

Click OK and you have done task.
Congratulations, the annoying Screen as in First telling that You Can't will never appear again.

Hope this will be helpful to you. If you found my articles helpful, write you feedback, suggestions or comments.

Happy to Be,
John Bhatt
PR's Blog


Guest Author: Demitri19 Sep 2013

hard combs not really relevant but i waiting at the dumb air port and the only web site I can get on to is this one good read anyway

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: