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 Object explorer pane:

    After 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. Or from Object explorer pane by clicking on Link static data menu item on selected table:

To unlink static data from the source control at later stage, uncheck that table in Static data form or by clicking on 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 on Action center with the name of its table with addition of “[Data]” tag:

Once it is committed to repository by one user, other users will see it on Action center but to track changes on it, they must also link it. It can be done by ways explained above or directly from Action center by clicking on “Static data is not linked for this table. Click to link”:

On the Action center developers can see any changes and apply it to or from source control repository the same way as changes on schema level.

In order to ensure the object and data structure between the database and the repository 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 the repository if any schema difference is detected between the selected table in the database and source control repository:

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 data

ApexSQL Source Control has a built-in mechanism which can reduce the performance impact when showing data scripts in the comparison window on the Action center. In case that data script has more than 10k rows, it will be divided on every tenth thousandth row. For example if script has 25k rows, it will have 2 pages of 10k and one of 5k:

There is one more option that can also reduce performance impact. It is Show different rows only option and can be accessed from ApexSQL Source Control options:

If checked, then only the script differences will be shown in the comparison window on the Action center:

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 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 has Custom script support which allows you to include static data with your own post deployment script.