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:

1. Creates a new table with the columns in the right order 
   
2. transfers the data, using INSERT INTO ... SELECT 
   
3. drops the old table 
   
4. renames the new table

There might be some other steps in there, depending on the design of your table; for example, it might disable foreign key relationships, re-enable an IDENTITY property, re-apply triggers, etc. It's nice for the GUI to do all of this for you, but it's bad that it will let you do it on larger tables, especially on production systems. 
 
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.