SQL Server 2008 R2 Add column into a specific location

I am using SQL Server 2008 R2 and I wish to add a n开发者_高级运维ew 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

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.

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.

There's no way I've seen to script what you are trying to do. However in SSMS (in v 10.5 at least) you can right click on a table in the object explorer, and choose Design. This permits you to insert a column anywhere in the column order. It also preserves things on your table such as FK references, etc.

I haven't researched to verify what SQL Server is actually doing behind the scenes and I don't use this for rollouts myself but it is there.

you can do this by right click the table and go to design and change the order by drag and drop.