SQL Server 2008 : Cannot Insert new column in the middle position and change data type

Question

My OS is Windows server 2008.

I've already installed SQL Server Express 2008.

I have several problems:

1. I can't insert a new column in the middle position. If I insert in the last one, I can save the table design.
2. I can change the column name but I can't change the data type.

I got error message : 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 recreated or enabled the option Prevent saving changes that require the table to be re-created.

Example:

I have ID, Name, Phone, and Status columns. I am unable to add Address between Name and Phone.

But, I can add Address if i place it after Status.

Is there any way to solve this problem?

Thanks before.

Answer 1

In SSMS Tools -> Options -> Designers you would need to uncheck the option "Prevent Saving Changes that require table re-creation" to allow you to do this in SSMS.

This will rebuild the table and so generally isn't worth the hassle if the table is at all large and will make deployment to production trickier.

If there are columns which logically you would prefer to have next to each other to make writing queries easier you can create a View with the desired column order.