Sql Server Table Column positioning

I added a column to my Sql Server Table by using T-SQL. However, I want to know can I reposition an existing column. In other words, I want to move my column I added to Column 1 position.  Can I do that using T-SQL.?
Who is Participating?
Gregory MillerConnect With a Mentor General ManagerCommented:
Cannot be done in TSQL...


 To change the column order

This task cannot be performed using Transact-SQL statements.
Gregory MillerGeneral ManagerCommented:
I am pretty certain that you must create a new table with the columns in the order you want and then copy the data from the old table to the newly created table. Then drop the old table to make this work.
In the end it does definitely have to be done by creating a new table, but it's still all going to be through tsql...

You can do this in Management Studio, if you open a table in design view and insert the column, you can hit the button for "Generate Change Script", which will give you the TSQL commands that it's going to run in order to make the change.

Gives you a better idea of what's involved.
Note that depending on how many indexes / FKs linking to the table, that change script could be huge.  As it's going to have to drop all of the constraints and indexes, transfer the data, and then recreate.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.