Adding a column immediately after a specific current column

Using mssql2017. Need to add a new column after an existing column. Tried below syntax , but its not working

Alter table mytable add mynewcolumn varchar(255) after myothercolumn

Any help is appreciated.
Thank you

When you add a column to an existing table, it is added as the last column, i.e., the last ordinal position. The only way to add a column between existing columns is to drop and recreate the table.

Code that relies on ordinal positions, in general, is not a good practice. The recommended practice is to always explicitly list the columns. That said, I know many situations where it would be useful to do what you are asking for, but unfortunately, SQL Server does not allow that.

1 Like

Why would you want to do such a thing?
In SQL Server you cannot add a column at a specific location. You should play dirty with Management Studio (but it's not always possible) or by creating a new table and moving the data into it.
However, in general I advise against doing this. You simply create a view that gives you the columns in the order you want.

If you use SSMS (the SQL gui) to change the table, you can add the column wherever you want. SSMS will generate a script to recreate the table with the columns in the order you want. WARNING: if the table is small, this will work fine. For a large table, it might time out or take a very long time.

If the table is (very) large, the only other way would be to create a new table with the new columns in the order you want, then copy the existing table into the new table (then finally drop the old table).

1 Like