Additional scripts
Published 31 July 2019
Additional scripts are idempotent (or "repeatable") scripts that allow you to include logic to deploy the same object more than once (that is, using DROP and CREATE statements). They should be runnable many times with no loss of state within the target database.
Additional scripts are similar to programmable objects. 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. For example, when you generate migrations, the scripts within the programmable objects folder are modified.
| Suitable object types | Unsuitable object types |
|---|---|
SQLCLR-related objects including assemblies and CLR procedures and functions | 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 |
Static table data, when a MERGE with VALUES statement is employed (see static data) | Tables (with the possible exception of computed columns, which are are a replaceable type of object) |
Server-scoped objects such as linked servers and scheduled jobs | Any other non-replaceable types of objects, or objects that have bindings to other objects (like a function used within a computed column) |
Deployment
Additional scripts execute against your database after all migration scripts have been run. They are run alongside programmable objects.
Configuration
Adding additional scripts folder
You can add additional scripts folders by editing your .sqlproj file and adding the additional script folder name to the <AdditionalScriptFolders> node.
<AdditionalScriptFolders>
Additional Scripts;
More Additional Scripts;
</AdditionalScriptFolders>Deployment order
Additional scripts are deployed alongside programmable objects. The deployment order is determined by alphabetical ascending order on the file path, taking into account both folder path and file name. To adjust the order of deployment between additional scripts, change the file name. Additional scripts are treated as programmable objects and their deployment order can also be customized in relation to other programmable objects (only available in the Visual Studio extension).
Transaction handling
By default all of your project's migrations will be deployed within a single user transaction. You can disable automatic transactions by setting the TransactionHandling attribute in the migration metadata to "Custom". See transaction handling for more information.
-- <Migration TransactionHandling="Custom"/>
