SSDT table column order

Question

I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema:

1. Adding a new column at the end of the table: "Table is changed"
2. Removing a column from table: "Table is changed"
3. Adding a column in between existing columns or just changing their order: "The recreation of the table was started"

(I did the translations myself, they maybe inaccurate but similar).

Step No. 3 takes forever during publishing process. I wonder why and what exactly is going on. I suspect the table structures are recreated entirely. I would not want this, I woul`d basically not care very much about the columns orders in the different target systems. Is there any way to prevent this time consuming process?

Edit:

Meanwhile I found that many people are struggeling with the same problem. SSDT does not have any option to ignore the column order of the target tables. That makes it re-arrenge all the data as described above. This is time consuming and entirely useless, but there is no choice if you want to use SSDT.

See & please upvote here:

Visual Studio Uservoice Item

Microsoft Connect Item

MSDN Forum Discussion

Answer 1

You are correct, the only way to "rearrange" the order of the columns is to create a table with the new structure and push the old data into it, drop the old table and then rename the new table (or some variation of that). It requires copying all the data in the table and some drops and renames.

Answer 2

Is there any way to prevent this time consuming process?

There sure is! Stop caring about column order. If it is that important, be willing to pay the price, or spend more time up front on the design so that you're not re-deploying the same table multiple times before you get the column order "right."

SSMS does the exact same thing if you use the table designer and change the order of columns - it will create a new table, copy all of the data, drop the old table, and rename the new one. This is not cheap - it takes time for the sheer physical data movement, and that movement has to be fully logged.

You'll notice, too, that there is no T-SQL / DDL command that allows you to specify the physical location of a new column; this is because SQL Server doesn't support it without rebuilding.

And it shouldn't really matter, anyway. Your application can be coded to ask for the columns in whatever order it wants, regardless of underlying physical implementation. If this is for the lazy SELECT * spot-checkers, create a view for them.