How to work with version controlled SQL Server database static data

Challenge

When working with SQL Server database static data in the context of version control, there are several key requirements.

1. As a pre-requisite, static data should be properly linked and initially committed to the repository. This is the process of initially loading (committing) the static data to a source control repository.

2. But once committed, there should be a means to track any changes related to the static data, no matter how frequent they are. In most cases, the static data won’t be changed (aka altered), but simply, new values will be added to the static tables and considered as a static data. No matter if the static data is updated, inserted or even deleted from the static tables, there should be a mechanism to instantly detect these changes and after reviewing, offer the user an option to commit these changes to the repository, or to revert back latest changes and apply them against a database. This will be the focus of this article.

3. The final requirement is getting static data out of the repository, as part of a database build and/or continuous integration/delivery process. This topic is out of the scope of this article, but is covered in other articles on Continuous integration and delivery in our Solution center. See Continuous integration for all related articles

Solution

ApexSQL Source Control is a SQL Server Management Studio add-in that allows the user to easily link and initially commit static data with few clicks.

For the purpose of this article, let’s introduce a particular use case of how to version control static data changes. When you are done with the part that describes how to initially commit the static data to the repository the steps shown below allow you to review any static data change and perform the commit/get operation, as needed. We’ll show how to update static data on the repository with any new records added to static data tables as well as how to revert any static data changes.

The Pubs sample database that is used in the abovementioned article will be used through this article, and the following tables are considered as static data tables: pub_info, sales and stores.

Making changes

Let’s make some data change in the stores table by executing the following SQL script against the Pubs database, specifically, against the sales table:

UPDATE [dbo].[sales]
SET [payterms] = 'Check'
WHERE title_id = 'BU1032'
GO

We have changed the payterms column by replacing the ON invoice value with the Check value. Assuming that this is a good change that we want to commit to the repository, let’s go ahead and do just that. After the static data change has been made, using the above UPDATE SQL statement, we’ll refresh the Action center tab, in order to bring up the new changes for reviewing:

In order to commit, we’ll provide a commit message and click the Apply button:

Reverting changes

Now, let’s make additional change in the static data in order to simulate how to revert static data changes if we are not satisfied with a change. The following SQL statement is executed against the Pubs database, specifically, against the stores table:

UPDATE [dbo].[stores]
SET [state] = 'ME'
WHERE stor_id = '6380'
GO

This change replaces the WA value in the state column with “ME” for Maine. Since this is not the correct change, we’ll revert it. In order to revert, refresh the Action center tab, to bring u the new change:

In order to revert changes, we’ll switch the Action column from right to left, and click the Apply button:

After applying changes, let’s query the stores table in order to verify that changes are properly reverted:

SELECT *
FROM dbo.stores

As a result, we’ll get the following:

As marked in the image above, WA is not changed to ME.

Since tables used so far do not have many records, we could easily load and review changes in the Action center tab, like we did with our payment terms table:

However, what happens in case a table contains a larger amount of data. Let’s bring up the jobs table in the Action center tab, since we initially committed the static data, and made some changes after that:

By looking at the bar on the left side of the Differences section, we can see that there are three changes in total, made against the jobs table. We can conclude that based on the three lines shown in the bar (two blue lines and one red line). However, we cannot see all differences without browsing through the SQL script and that can be time consuming, because the Differences section shows all records, no matter if there are differences or not.

In order to show different rows only, we’ll need to set this up in the add-in options, by checking the Show different rows only option, under the Action center tab, from the Static data section:

After refreshing the Action center tab, as a result rows that have differences are shown:

Using the described workflow, the static data can be tracked down for any changes in the similar way as for DDL changes. Also, the user can review any change and decide if it will be committed to the repository, or the latest change will be reverted back from the repository and applied against a database.