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

SqlSql ServerSql Server-2005

Sql Problem Overview

I need a SQL query which add a new column after an existing column, so the column will be added in a specific order.

Please suggest me if any ALTER query which do that.

Sql Solutions

Solution 1 - Sql

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:

ADD COLUMN  new_column <type>
AFTER       existing_column

Solution 2 - Sql

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.

Solution 3 - Sql

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: https://stackoverflow.com/questions/6692021/performance-space-implications-when-ordering-sql-server-columns/6692107#6692107

Solution 4 - Sql

If you want to alter order for columns in Sql server, There is no direct way to do this in SQL Server currently.

Have a look at http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/

You can change order while edit design for table.

Solution 5 - Sql

First add the new column to the old table through SSMStudio. Go to the database >> table >> columns. Right click on columns and choose new column. Follow the wizard. Then create the new table with the columns ordered as desired as follows:

select * into my_new_table from (
select old_col1, my_new_col, old_col2, old_col3
from my_old_table 
) as A

Then rename the tables as desired through SSMStudio. Go to the database >> table >> choose rename.

Solution 6 - Sql

According to my research there is no way to do this exactly the way you want. You can manually re-create the table and copy the data, or SSMS can (and will) do this for you (when you drag and drop a column to a different order, it does this). In fact it souldn't matter what order the columns are... As an alternative solution you can select the data you want in the order you desired. For example, instead of using asterisk (*) in select, specify the column names in some order... Lets say MyTable has col1, col2, col3, colNew columns.

Instead of:


You can use:

SELECT col1, colNew, col2, col3 FROM MyTable

Solution 7 - Sql

It is a bad idea to select * from anything, period. This is why SSMS adds every field name, even if there are hundreds, instead of select *. It is extremely inefficient regardless of how large the table is. If you don't know what the fields are, its still more efficient to pull them out of the INFORMATION_SCHEMA database than it is to select *.

A better query would be:

  When DATA_TYPE In ('varchar', 'char', 'nchar', 'nvarchar', 'binary') 
  Then convert(varchar(MAX), CHARACTER_MAXIMUM_LENGTH)  
  When DATA_TYPE In ('numeric', 'int', 'smallint', 'bigint', 'tinyint') 
  Then convert(varchar(MAX), NUMERIC_PRECISION) 
  When DATA_TYPE = 'bit' 
  Then convert(varchar(MAX), 1)
  When DATA_TYPE IN ('decimal', 'float') 
  Then convert(varchar(MAX), Concat(Concat(NUMERIC_PRECISION, ', '), NUMERIC_SCALE)) 
  When DATA_TYPE IN ('date', 'datetime', 'smalldatetime', 'time', 'timestamp') 
  Then '' 


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAayushiView Question on Stackoverflow
Solution 1 - SqlBrad ChristieView Answer on Stackoverflow
Solution 2 - SqlilansView Answer on Stackoverflow
Solution 3 - SqlgbnView Answer on Stackoverflow
Solution 4 - Sqlashish.chotaliaView Answer on Stackoverflow
Solution 5 - SqlwwmbesView Answer on Stackoverflow
Solution 6 - SqlMurat Can KurtView Answer on Stackoverflow
Solution 7 - SqlJake PogorelecView Answer on Stackoverflow