I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema:
- Adding a new column at the end of the table: "Table is changed"
- Removing a column from table: "Table is changed"
- 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:
Best Answer
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.