How do I change the order of columns in a table?
A very similar question: when I use ALTER TABLE ADD COLUMN, how can I insert the new column somewhere other than the "end" of the table?
My first question is, why does the order of the columns matter? Your SELECT statements should be listing the columns you want, in the order you want. We know that we shouldn't be using SELECT * in production code (see Article #2096), but if you have to, you can simply create a view using a SELECT statement with the columns in the right order; then you can SELECT * from the view, instead of from the base table. Likewise, INSERT statements without explicit column lists are bad practice, and should be avoided.
All that aside, you can achieve this, but not without moving the data — making it impractical on huge tables. Here is what Enterprise Manager essentially does, behind the scenes, when you choose "Insert Column" and save the table:
In my opinion, this kind of change is cosmetic and should be dealt with in a different way, once the system is up and running. During development, you can simply drop the table and re-create it with the columns in the desired order.
Related ArticlesCan I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?