SQL Server — optimization techniques for rebuilding very large table
Code that relies on the order of columns is asking for trouble anyway. It should never be a problem, since as a DBA, you're profiling and identifying SELECT * statement, and ostracizing the perpetrators, correct?

When I do have to perform such an operation, due to some dumb legacy software, I would follow these steps:


rename existing table and named constraints
create table with new schema
bulk insert from old to new table. if required, break on the id and do it in batches of 100,000* records using a while loop (* - a number that suits the hardware &amp; environment)
drop old table - reclaim space here
create indexes with proper fillfactors
create constraints (after indexes)