Working with static data

Applies to

ApexSQL Source Control

Summary

This article explains how to work with static data in ApexSQL Source Control.

Description

The main focus of the ApexSQL Source Control tool is to track changes in object structure (schema level). But, developers can also link table data to source control to track and compare changes, as well.

Static data, also known as reference or lookup data, is data that is necessary for the first deployment so that the application which is built upon that database can work properly. This data can be any set of predefined values that are rarely changed, e.g. postal codes, lists of states e.g. NY, etc.

There are two ways to link data:

  1. From the Static data window, which can be accessed from the database right-click context menu in the Object Explorer panel:

    Database right-click context menu data command

    All data in the database

    In this window, only tables with the Primary key will be listed. After the Link/Unlink button is clicked, data from all checked tables will be linked, while data from all unchecked tables will stay out of source control

    Quick tip icon

    Quick tip:

    When table data is linked, it doesn’t mean that it is committed to the repository, just that developers can start working with it through ApexSQL Source Control (commit, changes tracking…)

  2. From the Object Explorer panel, by clicking on the Link static data menu item for the selected table:

    Object right-click context menu data command

    Using this command on the tables without the Primary key will raise the following message:

    Message for the tables without primary key

    To unlink data from source control at a later stage, uncheck that table in the Static data window and click the Link/Unlink button or click the Unlink static data right-click context menu item on that table in the Object Explorer panel:

    Object right-click context menu data command

    Quick tip icon

    Quick tip:

    Unlinking data doesn’t mean that this data will be deleted from the source control repository. That way developers who are still working with that data can continue to work with it normally

No matter which way is chosen to link table data after its being linked, the object status icon color will be changed to ‘’yellow’’:

Object status icon for linked table data

The linked table data will be represented in the Action center tab with the name of its table with the addition of the “[Data]” tag:

Data in the Action center tab present only in the database

Once it is committed to source control, other users will see it in the Action center tab, but to track changes on it, they must also link it from a local machine. It can be done by ways explained above or directly from the Action center tab, by clicking the message: “Click to link static data”:

Data in the Action center tab present only in the repository

In the Action center tab, developers can see any changes and apply them to or from source control in the same way as changes on the schema level.

In order to ensure the integrity of an object and data structure between a database and source control and to prevent errors when working with the data tables (objects), ApexSQL Source Control will not allow applying any data changes to or from source control, if any schema difference is detected between the selected table in a database and source control :

Committing data change without schema change message

Applying data change without schema change message

This issue can be resolved by applying both schema and data changes at once, or applying the first schema and then data changes.

Working with a large amount of data

The option that can reduce performance impact is the Show different rows only option and can be accessed from ApexSQL Source Control options, under the Action center tab:

Data options in the Options window

If the option is checked, only rows where differences are detected will be shown in the Differences section of the Action center tab:

Only different data rows shown in the Action center tab

Updating static data scripts

  • Note: All users that linked and committed table data to the source control repository with any of the previous ApexSQL Source Control versions, will have to update the data scripts after an upgrade to the ApexSQL Source Control 2021 version is done

After the upgrade to the ApexSQL Source Control 2021 version is done and the Action center tab is opened for any database that has already linked table data, all data will be shown as ‘’only present on the repository (unlinked from database)’’:

Data in the Action center tab present only in the repository

The status object icon, for the tables with the linked data in any of the previous ApexSQL Source Control version, will be a blue dot showing that the data is not linked:

Object status icon for a table without data linked

Link table data, one by one, through the Action center tab by clicking on the ‘’Click to link static data’’ link for every listed table:

Linking data through the Action center tab

Or right-click on the linked database in the Object Explorer panel and choose the More source control options – Static data command. In the Static data window check desired tables and click the Link/Unlink button:

Linking data through the Data window

After the Action center tab is refreshed, the linked table data will be shown as a difference, with scripts present on the database and repository side:

Differences between database and repository data in the Action center tab

The shown differences are not the data differences, they are the differences in the way the data script is written. Check all data changes and click the Apply button to commit all those listed data changes to the source control repository. After this is done, the Action center tab will be refreshed and the database and the repository will be synced.

The Action center tab

From this point on, the update of the data scripts is done and the work with the data can continue.

Resolve data conflict

  • Note: This option is available from the ApexSQL Source Control 2021 version

When the data conflict is detected the following screen will be shown in the Action center tab:

Data conflict in the Action center tab

Here will be present three resolve conflict solutions – Keep local, Take from repository, and Merge:

Data conflict resolution options in the Action center tab

When the Keep local solution is chosen and the Confirm button is clicked, in the Action column the action will be changed to ’’Commit to source control’’ (arrow to the right) and the Apply button icon will follow that change when the change is checked:

Data conflict resolution options in the Action center tab - Keep local

Choosing the Take from repository solution will change the action to ’’Commit to database’’ (arrow to the left) and change the Apply button icon accordingly when the change is checked:

Data conflict resolution options in the Action center tab - Take from repository

  • Note: For now the merge data solution is not supported, if the Merge solution is chosen the following message will be raised:

    Merge data message

Based on the chosen resolve conflict solution the change, after the Apply button is clicked, will be committed to the source control repository (Keep local), or committed to the database (Take from repository).

FAQs

Q: How can I delete data from the source control repository?

A: Simply unlink the data table locally and commit that change on source control repository from the Action center:

Deleting data from the Action center tab

Additionally, anyone who has access to the source control repository with proper rights can delete the data files.

Q: Can I use this feature to track changes to all data in the database?

A: Yes, you can. But it is not recommended to be used with a large amount of data because it can be performance intensive

Q: Can I include data in builds e.g. with ApexSQL Build along with schema changes?

A: ApexSQL Build supports (can use) data committed using ApexSQL Source Control.