Changing the Column order/adding Newcolumn for existing Table in SQLServer2008

Question

Hi I have situation where I need to change the order of the columns/adding new columns for existing Table in SQLServer2008, 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 SQLServer 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

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.

Answer 3

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 4

As the other posters have said, there is no way without re-writing the table (but SSMS will generate scripts which do that for you).

If you are still in design/development, I certainly advise making the column order logical - nothing worse than having a newly added column become part of a multi-column primary key and having it no where near the other columns! But you'll have to re-create the table.

One time I used a 3rd party system which always sorted their columns in alphabetical order. This was great for finding columns in their system, but whenever they revved their software, our procedures and views became invalid. This was in an older version of SQL Server, though. I think since 2000, I haven't seen much problem with incorrect column order. When Access used to link to SQL tables, I believe it locked in the column definitions at time of table linking, which obviously has problems with almost any table definition changes.