Can I logically reorder columns in a table?

Question

If I'm adding a column to a table in Microsoft SQL Server, can I control where the column is displayed logically in queries?

I don't want to mess with the physical layout of columns on disk, but I would like to logically group columns together when possible so that tools like SQL Server Management Studio list the contents of the table in a convenient way.

I know that I can do this through SQL Management Studio by going into their "design" mode for tables and dragging the order of columns around, but I'd like to be able to do it in raw SQL so that I can perform the ordering scripted from the command line.

Answer 1

You can not do this programatically (in a safe way that is) without creating a new table.

What Enterprise Manager does when you commit a reordering is to create a new table, move the data and then delete the old table and rename the new table to the existing name.

If you want your columns in a particular order/grouping without altering their physical order, you can create a view which can be whatever you desire.

Answer 2

When Management Studio does it, it's creating a temporary table, copying everything across, dropping your original table and renaming the temporary table. There's no simple equivalent T-SQL statement.

If you don't fancy doing that, you could always create a view of the table with the columns in the order you'd like and use that?

Edit: beaten!

Answer 3

If I understand your question, you want to affect what columns are returned first, second, third, etc in existing queries, right?

If all of your queries are written with SELECT * FROM TABLE - then they will show up in the output as they are layed out in SQL. If your queries are written with SELECT Field1, Field2 FROM TABLE - then the order they are layed out in SQL does not matter.

Answer 4

It sucks that the order isn't directly editable in MSSQL. Probably like Mark, I don't like a table with unordered fields. It has nothing to do with the tables functionality, it's an aesthetic issue. Firebird has it but we're (the company) porting away from it.

Answer 5

It can be done using SQL, by modifying the system tables directly. For example, look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58912

However, I would not recommend playing with system tables, unless it's absolutely necessary.

Answer 6

I'm not sure I see the issue here. When I want the columns in a particular order (e.g. so that I can later 'clip' all the data straight into a UI control) then the SELECT clause is the place to do this. I sounds like you are referring to the fact your GUI SQL tool of choice always shows the columns in some default order, being the order they were created in real time (i.e. the most recent one to the right), and you don't like this: fair enough but I'd politely suggest that, rather than refactor the table, it might be time to find a new GUI SQL tool that does 'remember' your preferences :)

If I was looking after an application that was relying on getting columns in a particular implicit default order from a table then at the very least I'd want to find out whether the order is documented: if it was it would give me some comfort to know the vendor would be expected to give me at least one version's notice of any change to that default. Regardless, I'm pretty sure I'd want to rid the application of any SELECT * constructs at the earliest opportunity.