SQL Query to add a new column after an existing column in SQL Server 2005

Microsoft SQL (AFAIK) does not allow you to alter the table and add a column after a specific column. Your best bet is using Sql Server Management Studio, or play around with either dropping and re-adding the table, or creating a new table and moving the data over manually. neither are very graceful.

MySQL does however:

ALTER TABLE mytable
ADD COLUMN  new_column <type>
AFTER       existing_column

It's possible.

First, just add each column the usual way (as the last column).

Secondly, in SQL Server Management Studio Get into Tools => Options.

Under 'Designers' Tab => 'Table and Database Designers' menu, uncheck the option 'Prevent saving changes that require table re-creation'.

Afterwards, right click on your table and choose 'Design'. In 'Design' mode just drag the columns to order them.

Don't forget to save.

ALTER won't do it because column order does not matter for storage or querying

If SQL Server, you'd have to use the SSMS Table Designer to arrange your columns, which can then generate a script which drops and recreates the table

Edit Jun 2013

Cross link to my answer here: Performance / Space implications when ordering SQL Server columns?