Why You Cannot Insert Columns in the SSDT Table Designer


First off, I highly recommend using the table designer in SSDT.  This is for a few reasons:

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.

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.

Mark Wojciechowicz