Column order with SQL Change automation
JeromeL
Posts: 10 Bronze 4
Hi.
I was wondering how column order was taken into account under SCA. I just changed my project file options for column order like this :
<SyncOptionIgnoreColumnsOrder>False</SyncOptionIgnoreColumnsOrder>
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.
Thanks
Jerome
Tagged:
Answers
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:
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.
Software Engineer
Redgate Software