Column order with SQL Change automation


I was wondering how column order was taken into account under SCA.  I just changed my project file options for column order like this :

I made a test, adding a column somewhere in an existing table and imported the change with SCA VS extension. The script is only an ALTER TABLE xyz ADD line. I would have expected something like create a TMP_ table with the new column, copy the data, drop the old table and rename the TMP_. Usual stuff. (note : my column was nullable, so no migration was needed for data)

In Schema-model folder, the column is at the right place.

Will the column order be taken car of only at deploy, and that explains for the simple script? Should I write the script myself if I want to preserve the column order (would be a shame, since SQL Compare did that for me...) ?

I'm still experimenting with SCA, so I did not have the chance to test the deployment yet to see how it would behave.



  • Options
    Hi Jerome,

    As far as I can tell setting the option to false will create the temp table in the generated script. The option needs to be set before generating the script. When I did that I got a rebuild:
    difference table showing a table rebuild

    And the generated script was using a temporary table to copy the data with the correct column order.

    Note: You may need to close the project and reopen for the option change to be detected.

    Column order will NOT  be taken care of at deployment. All deployment will do is run your checked in scripts in order.
    Peter Gerrard

    Software Engineer
    Redgate Software
  • Options
    JeromeLJeromeL Posts: 10 Bronze 4
    Thanks Peter,

    I will take another look. Last time I tried it did not generate a script with a temporary table. Good to know column order will not be taken care of at deployment, I will pay attention to this.

  • Options
    The SyncOptionIgnoreColumnsOrder is commented out by default, so you just need to uncomment it if you want to use that option.
    Have you visited our Help Centre?
Sign In or Register to comment.