Home » Questions » Computers [ Ask a new question ]

Can I logically reorder columns in a table?

Can I logically reorder columns in a table?

"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."

Asked by: Guest | Views: 56
Total answers/comments: 4
Guest [Entry]

"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."
Guest [Entry]

"I think what everyone here is missing is that although not everyone has to deal with 10's, 20's, or 1000's instances of the same software system installed throughout the country and world, those of us that design commercially sold software do so. As a result, we expand systems over time, expand tables by adding fields as new capability is needed, and as those fields are identified do belong in an existing table, and as such, over a decade of expanding, growing, adding fields, etc to tables, and then having to work with those tables from design, to support, to sometimes digging into raw data/troubleshooting to debug new functionality bugs, it is incredibly aggravating to not have the primary information you want to see within the first handful of fields, when you may have tables with 30, 40, 50, or even 90 fields, and yes, in a strictly normalized database.
I've often wished I could do this, for this exact reason. But short of doing exactly what SQL does, building a Create Script for a new Table the way I want it, writing the Insert to it, then dropping all existing constraints, relationships, keys, index, etc etc from the existing table and renaming the ""new"" table back to the old name, and then reading all those keys, relationships, index, etc etc ....
It's not only tedious, time-consuming, but ... in five more years, it will need to happen again.
It's so close to worth that massive amount of work, however the point is, it won't be the last time we need this ability, since our systems will continue to grow, expand, and get fields in a wacked ordered driven by need/design additions.
A majority of developers think from a single system standpoint that serves a single company or very specific hard box market.
The ""off-the-shelf"" but significantly progressive designers and leaders of development in their market space will always have to deal with this problem, over and over, and would love a creative solution if anyone has one. This could easily save my company a dozen hours a week, just not having to scroll over, or remember where ""that"" field is in the source data table."
Guest [Entry]

"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 laid out in SQL.

If your queries are written with SELECT Field1, Field2 FROM TABLE - then the order they are laid out in SQL does not matter."
Guest [Entry]

"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!"