How to add a column in TSQL after a specific column?

/* Script to change the column order of a table
Note this will create a new table to replace the original table
HOWEVER it doesn't copy the triggers or other table properties - just the data
*/

Generate a new table with the columns in the order that you require

Select Column2, Column1, Column3 Into NewTable from OldTable

Delete the original table

Drop Table OldTable;

Rename the new table

EXEC sp_rename 'NewTable', 'OldTable';

solution:

This will work for tables where there are no dependencies on the changing table which would trigger cascading events. First make sure you can drop the table you want to restructure without any disastrous repercussions. Take a note of all the dependencies and column constraints associated with your table (i.e. triggers, indexes, etc.). You may need to put them back in when you are done.

STEP 1: create the temp table to hold all the records from the table you want to restructure. Do not forget to include the new column.

CREATE TABLE #tmp_myTable
(   [new_column] [int] NOT NULL, <-- new column has been inserted here!
    [idx] [bigint] NOT NULL,
    [name] [nvarchar](30) NOT NULL,
    [active] [bit] NOT NULL
)

STEP 2: Make sure all records have been copied over and that the column structure looks the way you want.

SELECT TOP 10 * FROM #tmp_myTable ORDER BY 1 DESC -- you can do COUNT(*) or anything to make sure you copied all the records

STEP 3: DROP the original table:

DROP TABLE myTable

If you are paranoid about bad things could happen, just rename the original table (instead of dropping it). This way it can be always returned back.

EXEC sp_rename myTable, myTable_Copy

STEP 4: Recreate the table myTable the way you want (should match match the #tmp_myTable table structure)

CREATE TABLE myTable
(   [new_column] [int] NOT NULL,
    [idx] [bigint] NOT NULL,
    [name] [nvarchar](30) NOT NULL,
    [active] [bit] NOT NULL
)

-- do not forget any constraints you may need

STEP 5: Copy the all the records from the temp #tmp_myTable table into the new (improved) table myTable.

INSERT INTO myTable ([new_column],[idx],[name],[active])
SELECT [new_column],[idx],[name],[active]
FROM #tmp_myTable

STEP 6: Check if all the data is back in your new, improved table myTable. If yes, clean up after yourself and DROP the temp table #tmp_myTable and the myTable_Copy table if you chose to rename it instead of dropping it.

You should be able to do this if you create the column using the GUI in Management Studio. I believe Management studio is actually completely recreating the table, which is why this appears to happen.

As others have mentioned, the order of columns in a table doesn't matter, and if it does there is something wrong with your code.

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.