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

0 votes
asked Oct 22, 2009 by himadri

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.

16 Answers

0 votes
answered Jan 22, 2009 by solairaja

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
0 votes
answered Oct 22, 2009 by lexu

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
0 votes
answered Oct 22, 2009 by marc-s

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.

0 votes
answered Oct 22, 2009 by thorsten

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.
0 votes
answered Oct 22, 2009 by mevdiven

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
-  -  -
0 votes
answered Oct 15, 2014 by robotik

As per http://msdn.microsoft.com/en-us/library/aa337556.aspx

This task cannot be performed using Transact-SQL statements.

Well, it can be, 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.

0 votes
answered Oct 23, 2014 by komma8-komma1

You can do it by creating a new table, copy all the data over, drop the old table, then renaming the new one to replace the old one.

You could also add new columns to the table, copy the column by column data over, drop the old columns, then rename new columns to match the old ones. A simple example below: http://sqlfiddle.com/#!3/67af4/1

CREATE TABLE TestTable (
    Column1 INT,
    Column2 VARCHAR(255)
);
GO

insert into TestTable values(1, 'Test1');
insert into TestTable values(2, 'Test2');
GO

select * from TestTable;
GO

ALTER TABLE TestTable ADD Column2_NEW VARCHAR(255);
ALTER TABLE TestTable ADD Column1_NEW INT;
GO

update TestTable 
set Column1_NEW = Column1, 
    Column2_NEW = Column2;
GO

ALTER TABLE TestTable DROP COLUMN Column1;
ALTER TABLE TestTable DROP COLUMN Column2;
GO

sp_rename 'TestTable.Column1_NEW', 'Column1', 'COLUMN';
GO
sp_rename 'TestTable.Column2_NEW', 'Column2', 'COLUMN';
GO

select * from TestTable;
GO
0 votes
answered Jan 1, 2015 by chagbert

At the end of the day, you simply cannot do this in MS SQL. I recently created tables on the go (application startup) using a stored Procedure that reads from a lookup table. When I created a view that combined these with another table I had manually created earlier one (same schema, with data), It failed - simply because I was using ''Select * UNION Select * ' for the view. At the same time, if I use only those created through the stored procedure, I am successful.

In conclusion: If there is any application which depends on the order of column it is really not good programming and will for sure create problems in the future. Columns should 'feel' free to be anywhere and be used for any data process (INSERT, UPDATE, SELECT).

0 votes
answered Jan 1, 2015 by karthik-m

you can use indexing.. After indexing, if select * from XXXX results should be as per the index, But only result set.. not structrue of Table

0 votes
answered Jan 22, 2015 by ravichandra-aj

You can achieve it with these steps:

  1. remove all foreign keys and primary key of the original table.

  2. rename the original table.

  3. using CTAS create the original table in the order you want.

  4. drop the old table.

  5. apply all constraints back to the original table

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...