Published 10 February 2016
Additional Scripts are a re-usable type of T-SQL script that allow you to include logic to deploy the same object more than once (i.e. using DROP and CREATE statements).
The concept of additional scripts is actually very similar to Programmable Object scripts. The difference being that you control the structure and content of the scripts within the nominated folders, as opposed to the programmable objects folder, which is managed by the SQL Change Automation tool-window (i.e. when you import changes within the tool-window, SQL Change Automation modifies the scripts within the programmable objects folder).
Additional Scripts can be included in your project build/deployment by adding a custom folder using the project properties window.
When to use Additional Scripts
Types of objects that are suitable for deployment as Additional Scripts:
- SQLCLR-related objects including assemblies and CLR procedures and functions
- Static table data, when a MERGE with VALUES statement is employed (see Static Data)
- Server-scoped objects such as linked servers and scheduled jobs
Types of objects that are NOT suitable for deployment as Additional Scripts:
- Programmable/code objects such as stored procedures, triggers, functions, etc (use the Programmable Objects feature instead to ensure that SQL Change Automation keeps in-sync with your objects)
- Tables (with the possible exception of computed columns, which are are a replaceable type of object)
- Any other non-replaceable types of objects, or objects that have bindings to other objects (like a function used within a computed column)
Please note that SQL Change Automation does not have any capability to generate scripts for the above types of objects/data. SQL Change Automation simply applies the deploy on change convention to allow you to include custom, repeatable scripts as part of your database deployment process.
Scripts that follow this convention need to be written in an idempotent way: they should be runnable many times with no loss of state within the target database.
How to add an Additional Scripts folder
By default, SQL Change Automation does not contain any additional script folders. In order to add one, first add it to your project using the Solution Explorer.
Then, right-click the project node in Solution Explorer and select Project Properties.
Scroll down to the Additional Scripts section of the Project Settings tab. A list containing a list of user folders will appear.
Check the newly-added folder to flag it as an additional scripts folder. Leave unchecked any folders that you do not wish to include in your deployment.
You can then add .sql files to your folder. These will be picked up at build time by SQL Change Automation, syntax checked and included as part of your deployment.
Controlling the order of execution
Additional Scripts execute against your database after all incremental migrations have been applied.
If you need to control the specific order that your Additional Scripts migrations are deployed, this can be set within the Project Properties by dragging and dropping items into the required order: