Need steps for Alter the table

Question

Whenever I make changes to a table structure in SSMS, there is a alert raised:

saving the changes is not permitted.the changes u have been made to the following tables to be dropped and recreate..

Answer 1

Some changes cause a table to be dropped and recreated. One such example is adding a column to the middle of a table rather than to the end.

You can do one of two things:

Option 1

Use TSQL to make your changes and add the column to the end (or the equivalent non table-drop option for your specific edit)

Option 2

Alter the default behavior of SSMS (warning - this is a very dangerous thing in production environments)

Open SQL Server 2008 Management Studio (SSMS). In the menu, go to Tools / Options. In the Navigation pane, expan Designers, and select "Table and Database Designers".

Under Table options, uncheck “Prevent saving changes that require the table re-creation” option and click OK.

Answer 2

I've seen those kinds of messages most often come up when the changes you are making could potentially cause data to be truncated. So, if you were changing an nvarchar(20) to nvarchar(10), that would come up. It doesn't matter if the column only contains data that is 2 characters long, it only looks at the data type. There may be other reasons and other flavors of that message, but mostly they follow the same reasoning: If the change would cause the table to be more restricted in the data it could hold, it wants to do radical surgery.