Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

T-SQL for changing table column order

Once a table is created, the ordinal position of the columns is fixed. This is especially problematic for large data warehouse tables, where new columns are expected to be added over time.

The only solution currently to re-order columns is to drop and recreate the table (or let the SSMS designer do this for you), but this is often not feasible for a large, highly used table, and it cannot be easily scripted.

Please add some ALTER syntax that will allow changing column order.

10 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Steve shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        To maintain alphabetical order when adding a column to a table, it is necessary to provide AFTER Column clause in the ALTER TABLE statement. It's absurd that Microsoft does not provide the ability to set proper ordinal position for a new column added to a table. Dropping and recreating the table means you get a current schema.sys.tables.create_date when all you want is a current schema.sys.tables.modify_date. Not to mention the requirement to add all the data back to the table after the drop / create they suggest.

      • Steven Hibble commented  ·   ·  Flag as inappropriate

        Although this would be a nice-to-have metadata operation, I think the workaround is probably all you need. Give your table a view. Select the columns in the order that you want.

      Feedback and Knowledge Base