7

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.

virboy
  • 105
  • 1
  • 2
  • 6
  • 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 May 25 '11 at 09:24
  • possible duplicate of [Changing the Column order/adding Newcolumn for existing Table in SQLServer2008](http://stackoverflow.com/questions/2489008/changing-the-column-order-adding-newcolumn-for-existing-table-in-sqlserver2008) – marc_s May 25 '11 at 09:26
  • regarding your edit and the error message you are seeing please read the very first line in my answer! – Martin Smith May 25 '11 at 10:10

4 Answers4

23

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
8

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 wish I could +100 this !! Column order is something from the dBase days - long gone times in the 1980s...... – marc_s May 25 '11 at 09:24
  • It does make a slight difference. The initial `leaf_offset` in `sys.system_internals_partition_columns` is taken off column order so it defines the initial order within the fixed/variable section (though `ALTER COLUMN` can cause this to change). This can make a slight difference to `varchar` columns as ones at the end of the section that are null or empty consume no space even in the column offset array. – Martin Smith Oct 26 '11 at 10:43
  • @Martin Smith: [have you been reading again](http://rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood/)? :) Using SSMS to insert a column will re-create the table, adding a new column at the end doesn't make a difference. Interestingly, is it possible to overflow the 8060 byte limit with repeated ALTER COLUMNs that leave "dead space" in a row...? – gbn Oct 26 '11 at 11:00
  • @gbn - Yes but the Kalen Delaney SQL Server 2008 book. Nice link thanks. Dropping a column can lead dead space causing overflow even if the column was dropped while the table is empty [as covered here](http://www.simple-talk.com/community/blogs/andras/archive/2009/02/19/72068.aspx). For this to happen via `ALTER COLUMN` I think it would need to have interleaved `ALTER COLUMN`s for different columns. – Martin Smith Oct 26 '11 at 11:24
  • @Martin Smith: I was more thinking of the 8060 byte limit with sufficient dead space, rather then reclaiming the space which I;d do anyway. This is why [DBCC CLEANTABLE](http://stackoverflow.com/questions/807579/how-to-reduce-size-of-sql-server-table-that-grew-from-a-datatype-change/808368#808368) or DBCC REINDEX has been recommended after dropping columns forever. Nowadays we have ALTER TABLE ..REBUILD or ALTER INDEX of course. Oh, and do you chat? http://chat.stackexchange.com/rooms/179/the-clustered-index – gbn Oct 26 '11 at 11:26
  • `CREATE TABLE #Foo (Filler1 char(4000),Filler2 char(4000)) alter table #Foo alter column Filler1 char(4001)` fails. Is that what you mean or am I completely misunderstanding your point? – Martin Smith Oct 26 '11 at 11:31
  • no, that's right. `CREATE TABLE #Foo (Filler1 char(4001),Filler2 char(4000))` would work of course: no dead space – gbn Oct 26 '11 at 11:37
0

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.

Pankaj Agarwal
  • 11,191
  • 12
  • 43
  • 59
0

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.

Duncan Howe
  • 2,965
  • 19
  • 18