Programmable objects
Published 31 July 2019
The following types are considered as programmable objects:
- Stored procedures
- Views
- User defined functions
- DDL triggers
Programmable objects are stored as idempotent (or "repeatable") migration scripts. This means that instead of unnecessarily incrementing each programmable object version as part of a deployment, only the latest definition is ever deployed. This approach allows changes to these objects to be branched, merged and annotated.
Deployment
When deploying a SQL Change Automation project, each programmable object is checked and executed only if the object is new or its definition has changed. Each database keeps track of the programmable objects version in the migration log by storing a checksum. When a deployment is run, a checksum is generated based on the programmable object definition in the project and compared with the target database migration log. If these do not match, the programmable object script is executed.
Programmable objects are updated after all migrations have been run. This means you can couple a table change (for example, adding of a column) and the associated procedure change in the one deployment.
If a programmable object is dropped, the appropriate drop code is reflected in a migration script and the associated programmable object file is deleted.
Recommendations
We recommend disabling Programmable Objects in SQL Change Automation if you are using a shared development database with a distributed version control system such as Git.
Configuration
How programmable objects are scripted out
The approach taken to scripting out programmable objects is determined by the ProgrammableObjectHandling setting in the project settings file. There are three possible values:
ScriptInMigrations- This effectively disables the programmable object feature and scripts all programmable object types out in migration scripts, as with any other object type.- You may wish to configure SQL Change Automation to script out these objects to the offline schema model by configuring the SyncToOfflineSchemaModelObjectTypes setting.
- Note that if you have already generated programmable objects before selecting this setting, these objects will continue to be deployed. If this is not desirable, delete the programmable objects folder.
UseRepeatableScriptsForAllObjects- This is the default setting. All programmable object types are scripted out as individual programmable object scripts during the baseline process and any subsequent incremental edits to these objects are handled in the same manner. These objects will not be scripted out to the offline schema model. This options is not recommended when using a SQL Clone image as a baseline because all programmable object scripts must be deployable and all scripts will be deployed on the first deployment to a target database. One of the benefits of using SQL Clone as a baseline is that not all objects in the baseline state need to be deployable.UseRepeatableScriptsForModifiedObjects- Programmable objects are scripted out in the offline schema model during baseline, but to the programmable objects folder thereafter. Any modified programmable object script will be removed from the offline schema model, if it exists there, and added to the programmable objects folder, so that these objects are not unnecessarily duplicated in your version control system. The main benefit of this option is that programmable objects in the baseline state do not need to be deployed when deploying to a target database for the first time. This is especially applicable when using a SQL Clone image as a baseline but may also be highly desirable if the baseline state contains a large number of programmable objects.
<PropertyGroup> <ProgrammableObjecthandling>UseRepeatableScriptsForModifiedObjects</ProgrammableObjecthandling> </PropertyGroup>
Deployment order
When programmable objects are updated, the dependencies of all programmable objects will be calculated and a suggested execution order will be written to the project configuration. This ensures that when the project is deployed any objects with dependencies will be created in the right order. To enable this behavior in earlier SQL Change Automation projects set the EvaluateExecutionOrderOnImport setting in the project settings file. Note that this setting will be disabled if you customize the order.
<PropertyGroup> <EvaluateExecutionOrderOnImport>True</EvaluateExecutionOrderOnImport> </PropertyGroup>
Note that if you customize the deployment order (only available in Visual Studio UI), this setting will be set to False.
Grouping
By default, SQL Change Automation groups the objects in your programmable objects folder by schema and then by object type (for example, Stored Procedures\dbo\MyProc.sql). If, however, you would like to revert to the default SQL Compare behavior, grouping only by object type and prefixing each file with the appropriate schema name (for example, Stored Procedures\dbo.MyProc.sql), you can do so by changing the DeployChangesImportSchemaFolders setting in the project settings file.
<PropertyGroup>
<DeployChangesImportSchemaFolders>False</DeployChangesImportSchemaFolders>
</PropertyGroup>Permission handling
SQL Change Automation scripts each of your programmable objects using DROP and CREATE statements. This means that, whenever you deploy your object, the permissions assigned to that object will be reset.
To avoid losing the permissions assigned to your objects in each deployment, SQL Change Automation appends GRANT/REVOKE statements to each programmable object script, based on the database environment that you imported the scripts from. This works for permissions that are stored in source control, however if you need to control permissions at the object level directly in the target environments, we recommend assigning the permissions via role objects, rather than assigning them at the individual user/group level.
If the assigning of user permissions directly in your databases is unavoidable, you might want to consider enabling the UseCreateOrAlterForRerunnableScripts option.
Alternatively you can follow a "pure" migrations approach, instead of using programmable objects. This approach results in most stored procedure/view/function/etc changes being made using the ALTER statement, which preserves the permission structures between deployments.
Transaction handling
By default all of your project's migration scripts and programmable objects 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"/>
