SQL Server 2008 R2 Add column into a specific location

Question

I am using SQL Server 2008 R2 and I wish to add a new column into a specific spot in the column order in a table and not at the end. I wish to avoid having to recreate the table. How do I do this without recreating the table?

i.e.

Table: Bob
==========
   Col1
   Col2

Add new Column.

Table: Bob
==========
   Col1
   NewCol
   Col2

Answer 1

You cannot. Column are always added at the end of the column list. The only way to change the order is to recreate the table from scratch.

That being said, it should never ever matter to you what is the actual physical order of the columns, nor the logical order of column definitions. If you have dependencies on column order, your code is broken. If you expect performance gains from column order, those are myths.

Answer 2

Don't do it. This goes together with the advise to never use SELECT *, in which case since you are listing all columns why does it matter their internal order?

That said, if you absolutely must, here's a demo that doesn't drop the table. The columns have to be dropped though, since you can't insert in the middle.

create table BOB(col1 int, colspace int, col2 int, col3 varchar(10))
insert BOB values (1,3, 2,'test')
;
alter table BOB add col2_copy int, col3_copy varchar(10), NewCol datetime
;
update BOB set col2_copy = col2, col3_copy = col3
;
alter table BOB drop column col2
alter table BOB drop column col3
;
alter table BOB add col2 int, col3 varchar(10)
;
update BOB set col2 = col2_copy, col3 = col3_copy
;
alter table BOB drop column col2_copy
alter table BOB drop column col3_copy
;

select * from BOB

It becomes significantly more difficult once you have constraints and defaults involved.