Add a new Column and define its position in a table

I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.

StackExchange Database Administrators Q#19536, answer score: 17