First off, I highly recommend using the table designer in SSDT. This is for a few reasons:
- Highlighting - When you highlight an index or foreign key, the columns involved in that object are highlighted
- Context view of the Scripts - Also, for the above, the definitions are shown in the script window, whether they are actually included in the table script or not.
- Customizable columns - You can customize the view to show some additional columns, and these columns are editable right in the table designer (I've always been annoyed at the identity column setting in SSMS):
Remember the table designer in SSMS? That had the ability to open a table in design mode, right click in the middle of the table and insert a column out of sequence. Then you could save the table, and voila, changes applied.
- Formatting - The table script is auto formatted
- Naming conventions - i.e. Right clicking to set an identity column will name the constraint PK_<TableName>
- It makes developers behave smarter - this part I will explain next
What happens behind the scenes is that a script is executed to rebuild the table with the new column order specified. If there is lots of data in the table, that save action will take a long time to execute, as all the rows are poured from the original table into the new version.
On the other hand, I am one to keep things neat and organized and adding a "forgotten" column to the end of the table has always irked me. So when I noticed this feature blatantly missing from the SSDT table designer, I thought it was an oversight and would be included in a later release:
Having used SSDT for a while now and implementing CI and CD on several projects with it, I have changed my mind about all that. This omission of the insert feature is actually quite intentional. The purpose is to make if difficult to do something bad and cause unnecessary data movement. So what if a new column is appended to the end of the table? You can certainly write your queries differently or throw a view on top. This is the behavior that SSDT encourages. It does nor prevent you from changing the script to the column order you want - but it makes it more work to do.
So the key take away is that, as we refactor databases, we must be very intentional about the changes we make. Every action we take, we should focus on minimizing data movement and destruction and favor actions that will add and create.