6

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

Improve this question

6 Answers 6

Reset to default
9

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??

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Column Order change, is just for viewin purpose same requested from Client
@rmdussa I never let clients have any permissions on tables in the first place. I don't typically treat table definitions as part of the definition of the services my database provides. Thus it is free for me to change and I do not have to commit to any particular table design for purposes of presentation. Views, table-valued functions, stored procs etc are the interface my databases typically expose.
@rmdussa: well, in that case, just list the fields needed in the required order in your SELECT statement - nothing else is required for that...
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.

Improve this answer

2 Comments

I would definitely not recommend mucking around with the "sys*" system catalog tables! You're just asking for trouble....
I already have written that it is not recommended. But as I understand it is exactly that author of question wants to do. Because only one possible way to cahnge collumns order without adding/removing columns - updating syscolumns table in DB.
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.

Improve this answer

1 Comment

In very cases order is important for example when we use some ORM such as EF with DB First approach and i want to publish web app on more than one host. Additionally i want a clear view in my DB when i look at tables and diagrams. I don't need to see some tables and imagine other sorting.
1

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.

Improve this answer

Comments

1

I think the simplest way would be re-create the table the way you want it with a different name and then copy the data over from the existing table, drop it, and re-name the new table.

Improve this answer

Comments

0

Would it perhaps be possible to script the table with all its data. Do an edit on the script file in something like notepad++

Thus recreating the table with the new columns but the same. Just a suggestion, but it might take a while to accomplish this. Unless you write yourself a small little c# application that can work with the file and apply rules to it.

If only notepadd++ supported a find and move operation

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.