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 of object structure (schema level). But, developers can also link static data to source control to track and compare changes, as well.

Static data, also known as reference or lookup data, is data which 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 static data:

  1. From the Static data form, which can be accessed from database context menu in the Object explorer pane:

    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 static 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 pane, by clicking on Link static data menu item for the selected table:

To unlink static data from source control at later stage, uncheck that table in the Static data form or by clicking the Unlink static data menu item on that table in Object explorer pane:

Quick tip icon

Quick tip:

Unlinking static 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.

After the static data is linked, it will be represented in the Action center tab with the name of its table with addition of the “[Data]” tag:

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: “Static data is not linked for this table. Click to link”:

In the Action center tab, developers can see any changes and apply it to or from source control in the same way as changes on 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 static 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 :

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

Working with large amount of static data

ApexSQL Source Control has a built-in mechanism which can reduce the performance impact when showing data scripts in the Action center tab. In case static data script is larger than the amount that can be shown, the following message appears:

Upon confirmation, the script opens in a separate window, so the actual static data can be reviewed before committing.

Another option that can reduce performance impact is the Show different rows only option and can be accessed from ApexSQL Source Control options:

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

FAQs

Q: Can I link data from any table?

A: Only data from tables with a Primary key can be linked.

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

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

Additionally, anyone who has access to the source control repository with proper rights can delete the static 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 large amount of data because it can be performance intensive

Q: Can I merge static data changes if a conflict occurs?

A: Not for now, but this will be implemented in one of the future releases.

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

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