Changing the column order/adding new column for existing table in SQL Server 2008

Question

I have situation where I need to change the order of the columns/adding new columns for existing Table in SQL Server 2008. It is not allowing me to do without drop and recreate. But that is in production system and having data in that table. I can take backup of the data, and drop the existing table and change the order/add new columns and recreate it, insert the backup data into new table.

Is there any best way to do this without dropping and recreating. I think SQL Server 2005 will allow this process without dropping and recreating while changing to existing table structure.

Thanks

Answer 1

You can't really change the column order in a SQL Server 2008 table - it's also largely irrelevant (at least it should be, in the relational model).

With the visual designer in SQL Server Management Studio, as soon as you make too big a change, the only reliable way to do this for SSMS is to re-create the table in the new format, copy the data over, and then drop the old table. There's really nothing you can do about this to change it.

What you can do at all times is add new columns to a table or drop existing columns from a table using SQL DDL statements:

ALTER TABLE dbo.YourTable
    ADD NewColumn INT NOT NULL ........

ALTER TABLE dbo.YourTable
    DROP COLUMN OldColumn

That'll work, but you won't be able to influence the column order. But again: for your normal operations, column order in a table is totally irrelevant - it's at best a cosmetic issue on your printouts or diagrams..... so why are you so fixated on a specific column order??

Answer 2

There is a way to do it by updating SQL server system table:

1) Connect to SQL server in DAC mode

2) Run queries that will update columns order:

update syscolumns 
set colorder = 3
where name='column2'

But this way is not reccomended, because you can destroy something in DB.

Answer 3

One possibility would be to not bother about reordering the columns in the table and simply modify it by add the columns. Then, create a view which has the columns in the order you want -- assuming that the order is truly important. The view can be easily changed to reflect any ordering that you want. Since I can't imagine that the order would be important for programmatic applications, the view should suffice for those manual queries where it might be important.