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

7
votes

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.

4
sqlsql-server-2008database-design
This is no problem - the order of the columns is absolutely without any significance in a SQL Server database table. Just add new columns at the end - in your SELECT statement, you can specify any order in which you want to columns listed. – marc_s
possible duplicate of Changing the Column order/adding Newcolumn for existing Table in SQLServer2008 – marc_s
regarding your edit and the error message you are seeing please read the very first line in my answer! – Martin Smith

4 Answers

22
votes

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.

8
votes

Column order doesn't matter either in the designer or in sys.columns.

The on disk storage will be the same regardless: Inside the Storage Engine - Anatomy of a record.

There is no performance benefit either.

0
votes

I think using query it's not possible, but you do using UI of SSMS. right click on selected table and Insert Column whenever you want.

Think it does not matter columns order.

0
votes

If you want a script to do this, all you need to do is select the data out into a temporary table, drop the table, recreate it with the columns in your preferred order and then reinsert the data from the temporary table in the right order.