ALTER TABLE ADD Column question

This has always puzzled me, so I really just wanted to know if it's possible through T-SQL.

TableA has 3 columns:

ColumnA
ColumnB
ColumnD

Is there anyway through T-SQL to add a new ColumnC *between* ColumnB and ColumnD.

ALTER TABLE TableA
ADD ColumnC [varchar](1)

Thanks.

As far as I know, there is no direct way to do this

You can do like this

Select * into #temp from TableA

drop table TableA

Create table TableA(ColumnA varchar(10),ColumnC varchar(10),ColumnB varchar(10))

Insert into TableA(CoulmnA,ColumnB)

select * from #temp

You have to recreate Any Primary Keys,constraints, Indexes existing on the table

|||

Thanks, that appears to be the only way, by using a temporary secondary table and copying the data over.

I thought the GUI was doing something special, but I've done some tests and confirmed it takes just as long.

|||SQL Server GUI might be updating the System Catalogs Instead of the above procedure, In that case It will be faster than the Normal procedure