Changing Column Ordinal_position Positions

Question

My scenario:

I can change the ordinal position of a column in a table.Is there a way to change the ordinal position of a column in a table without recreating the table?

Answer 1

No, you have to recreate the table if you wish to achieve this. (SQL SERVER)

Even when you do this in SSMS, you will see that the script that is generated also recreates the table.

Answer 2

Why would you want to do that in the first place? For queries and inserts simply specify column order.

Answer 3

Find More : ALTER TABLE syntax for changing column order

Changing the order of columns in a table requires the table to be dropped and recreated. As i look at the system tables, the order is simply stored as an integer but to change it requires a potentially costly drop and recreate where existing data must be offloaded to temp tables.

Answer 4

Hi it depends on the database system you use. For example in some it is possible to remove and add a column and you can do it in a procedure part where you also can refill it.

But in general it shouldn't matter as you can define the returned data order in your select statement. Is not that enough for you?

Answer 5

Not in SQL Server - Not sure about other RDBMSs.

You can create a View with the desired ordinal positions but the only time I can think that would be useful is if you are using SELECT * which is a practice that should be avoided anyway.