Hi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.
With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.
SQL Source Control introduced pre- and post- scripts in v6.3.
If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.
SQL Change Automation:
Supports column filtered static data tables in the SCA plugin in SSMS
Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts
If there are other use cases where a migrations approach would add value to the team – such as easily being able to run data modification statements as part of deployments, or being able to customize the TSQL for schema changes – then changing from SQL Source Control to SQL Change Automation might make sense.
Hi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.
With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.
SQL Source Control introduced pre- and post- scripts in v6.3.
We have a scenario where we have table maintenance screens in a user interface to allow the user to add/modify/delete lookup type values. As part of our application we provide a number of lookup values that are system defined and cannot be modified. We would like to be able to source control the system defined values, but also allow for user defined values to be unaffected. It does not make sense for us to use a separate table for the user defined lookup values because that would complicate the data schema and application design. It would help us a lot if we were able to source control static data that is filtered by a where clause (e.g. IsSystemDefined = 1 in our case).
We have a scenario where we have table maintenance screens in a user interface to allow the user to add/modify/delete lookup type values. As part of our application we provide a number of lookup values that are system defined and cannot be modified. We would like to be able to source control the system defined values, but also allow for user defined values to be unaffected. It does not make sense for us to use a separate table for the user defined lookup values because that would complicate the data schema and application design. It would help us a lot if we were able to source control static data that is filtered by a where clause (e.g. IsSystemDefined = 1 in our case).