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

Question

Hi all,

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

I want to rearrange column order in a table using sql query.

Please help.

Answer 1

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

Answer 2

Use

SELECT * FROM TABLE1

which displays the default column order of the table.

If you want to change the order of the columns.

Specify the column name to display correspondingly

SELECT COLUMN1, COLUMN5, COLUMN4, COLUMN3, COULMN2 FROM TABLE1

Answer 3

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.

Answer 4

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 the alter 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.

Answer 5

You can of course change the order of the columns in a sql statement. However if you want to abstract tables' physical column order, you can create a view. i.e

CREATE TABLE myTable(
    a int NULL,
    b varchar(50) NULL,
    c datetime NULL
);


CREATE VIEW vw_myTable
AS
SELECT c, a, b
  FROM myTable;

select * from myTable;
a  b  c
-  -  -

select * from vw_myTable
c  a  b
-  -  -