Table Column Order When Adding a New Column to an Existing Table

rbigbie

We already have an existing table which has a migration script for it in our ReadyRoll project. When we go to add an additional column in the middle of the existing columns, when ReadyRoll generates the migration script for the change it places the newly added column to the end of the column list. Why? How can we use ReadyRoll to correctly place the column in the place where we added it in the column list? This is very frustrating. Our column order in our tables are important for us, why would ReadyRoll change the order and not have the column order as it is in the database per our changes? Can you help us with this problem? Thank you.

dnlnln

As you've noted, by default ReadyRoll does not enforce strict column ordering when generating scripts. The reason for this is because preserving the column order may require a table rebuild, which may introduce a performance overhead when dealing with larger data-sets. It can also reduce a team's ability to work on schema changes in parallel, as enforcing column order turns an incremental single-statement operation (e.g. ALTER TABLE ADD COLUMN) into a complex multi-statement operation.

However you can have ReadyRoll preserve the column order by adding the following lines to your sqlproj file and restarting Visual Studio:

<PropertyGroup>
<SyncOptionIgnoreColumnsOrder>False</SyncOptionIgnoreColumnsOrder>
</PropertyGroup>

The change in behavior will apply to any new scripts that are generated.

For more information on customizing script generation options, please see this article in the documentation.

rbigbie

dnlnln, Thank you very much for the response and the detailed explanation. It's great to know that ReadyRoll has a configuration option for this in the sqlproj file. Again, I appreciate the assistance, awesome job.

dnlnln

My pleasure, glad to be of assistance! Please let me know if there's anything else I can help with.