How to change column order in a table using sql query in sql server 2005?

Question

You cannot. The column order is just a "cosmetic" thing we humans care about - to SQL Server, it's almost always absolutely irrelevant.

What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLE command, copying over the data from the old table, and then dropping it.

There is no SQL command to define the column ordering.

You have to explicitly list the fields in the order you want them to be returned instead of using * for the 'default' order.

original query:

select * from foobar

returns

foo bar
--- ---
  1   2

now write

select bar, foo from foobar

bar foo
--- ---
  2   1

according to https://docs.microsoft.com/en-us/sql/relational-databases/tables/change-column-order-in-a-table

This task is not supported using Transact-SQL statements.

Well, it can be done, using create/ copy / drop/ rename, as answered by komma8.komma1

Or you can use SQL Server Management Studio

1. In Object Explorer, right-click the table with columns you want to reorder and click Design (Modify in ver. 2005 SP1 or earlier)
2. Select the box to the left of the column name that you want to reorder. (You can select multiple columns by holding the [shift] or the [ctrl] keys on your keyboard.)
3. Drag the column(s) to another location within the table.

Then click save. This method actually drops and recreates the table, so some errors might occur.

If Change Tracking option is enabled for the database and the table, you shouldn't use this method.

If it is disabled, the Prevent saving changes that require the table re-creation option should be cleared in Tools menu > Options > Designers, otherwise "Saving changes is not permitted" error will occur.

• Disabling the Prevent saving changes that require the table re-creation option is strongly advised against by Microsoft, as it leads to the existing change tracking information being deleted when the table is re-created, so you should never disable this option if Change Tracking is enabled!

Problems may also arise during primary and foreign key creation.

If any of the above errors occurs, saving fails which leaves you with the original column order.

This is similar to the question on ordering the records in the result of a query .. and typically no one likes the formally correct answer ;-)

So here it goes:

• as per SQL standard, the columns in a table are not "ordered"
• as a result, a select * does not force the columns to be returned in a particular order
• typically, each RDBMS has a kind of "default" order (usually the order that the columns were added to the table, either in the create table' or in thealter table add ` statements
• therefore, if you rely on the order of columns (because you are using the results of a query to poulate some other datastructure from the position of the columns), explicitly list the columns in the order you want them.