Over time, your SQL Change Automation project may fill up with a large number of scripts from different releases.

Organizing your migrations into smaller groups makes your project easier to navigate as it scales.

Selecting migration grouping

By default, migrations are grouped by file path. If you wish to change that:

1. Open the project properties for the project (right click on the project in the solution explorer and select properties)
2. Select your preferred option in the Migration Grouping section

File path

You would want to use file path ordering when you want to group work together based on when its done, but do not want to restrict your folder structure.

Here it is easy to visualize how your scripts will be executed as it is the same order as they appear in the solution explorer.

In situations where scripts are likely to be reordered or generated in locations other than the end, using sub-folders can help, as new scripts can easily be added at the end of any folder.

Note that file prefixes need not be unique so scripts can also be inserted into earlier locations within a folder without renaming all scripts which follow.

Execution Order

Scripts will be run in the order you see in the solution explorer

Semantic version folders

The main reason why you may want to use semantic version ordering in your projects is for release planning. For example, say your organization uses the convention of branching by release. Each release branch is given a unique reference number (e.g. RL123), which can be used to look up the release in a change management/control system.

This value is appended to the version number, which is incremented within the major/minor/patch format.

Format

Semantic version folders adhere to the Semantic Versioning 2.0.0 specification.

The format is Major.Minor.Patch-PreRelease+Build.

The PreRelease and Build parts are optional.

Each part of the Semantic Version folder structure supports different characters. Major, Minor, and Patch only support digits (0-9). PreRelease and Build support digits, characters (a-z, case insensitive), dots (.) and hyphens (-).

Examples:

• 1.2.3-alpha
• 1.2.3-alpha.beta
• 1.2.3+abc
• 1.2.3-alpha+abc.def

Note that Semantic versioning ordering is not quite the same as file path ordering. With this ordering scripts in folder 10.0.0 will execute after folder 9.0.0 and scripts in folder 10.0.0-PreRelease will execute before folder 10.0.0.

Execution Order

SQL Change Automation projects with semantic versioning enabled will execute migrations in the following order:

1. The version number (i.e. the folder name)
2. The filename prefix (e.g. 001, 002, 003 etc)
3. The filename suffix (e.g. 20131123-1420_ryanp.sql)

File name, ignoring folders

This allows you to group migrations by what they are doing, rather than the order they are going to run.

Execution Order

Scripts will be ordered by file name, regardless of the folder they are in.