Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I logically reorder columns in a table?

Tags:

sql-server

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.

like image 644
fastcall Avatar asked Aug 04 '08 21:08

fastcall


People also ask

Does the order of columns in a table matter?

Yes, column order does matter.

Can you query columns in any order?

The order doesn't matter, actually, so you are free to order them however you'd like. edit: I guess a bit more background is helpful: As far as I know, the process of optimizing any query happens prior to determining exactly what subset of the row data is being pulled.


1 Answers

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.

like image 73
vzczc Avatar answered Sep 20 '22 17:09

vzczc