0

Is there any option in SQL server 2016 to change column order still, without creating a temp table, and recreating/reinserting whole table? Link below is for 2005.

We have a 500 Million row table in data warehouse. We want to insert a column in the middle. Either we can recreate the table, or utilize 300+ views for all our tables, which have similar situation. The view becomes another meta data presentation layer we have to manage. Wish sql server is smart enough to change column orders easier like Aurora or Postgresql.

How to change column order in a table using sql query in sql server 2005?

  • we have users/clients querying a Data Warehouse, don't want manage 300+ views for column order, every time column added, removed, etc, more about a presentation layer and maintenance issue –  Apr 05 '19 at 07:54
  • @AaronBertrand wondering how Aaron Bertrand would handle the issue, if create hundred plus views for data warehouse? thinking probably not reading article at this point https://dba.stackexchange.com/questions/61978/how-to-change-the-column-order –  Apr 05 '19 at 08:18
  • Why does the table need to be in a different order? Can you not just re-order you query? E.g. `select col1, col2, col5, col3, col4 from table` – iamdave Apr 05 '19 at 08:25
  • in comments, above I explain, its regarding presentation layer for our clients querying the database –  Apr 05 '19 at 14:36

1 Answers1

0

Is there any option in SQL server 2016 to change column order still, without creating a temp table, and recreating/reinserting whole table?

No. Column ordinals in SQL Server control the visible order of the columns and the physical layout of the data.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Hi David, thanks, let me know if you have any answers for this also https://stackoverflow.com/questions/55484707/power-bi-slow-with-ssas-multidimensional-cube –  Apr 08 '19 at 21:25