How to link and initially commit SQL Server database static data

Challenge

Non-transactional data in tables that is never (or rarely) changed, for example currency codes, postal codes or personal ID numbers, is often referred to as “static data” (also known as lookup or reference data). Although static data isn’t part of the database schema, it can be thought of in much the same way. Simply creating test data for static tables, for testing purposes, may allow the tests to be successfully executed, but often won’t render meaningful looking results and in some cases, could result in failure, if the static data was tightly coupled to application/database function. Static data tables also tend to be much smaller than transactional tables.

For all of these reasons (and more) static data can, and should be versioned under source control and any changes tracked.

This article is a guide through the process of linking and committing static data to a repository. Comparing static data and pulling changes from the repository are out of the scope of this article, but will be covered in a subsequent one.

Solution

We are going to link and commit SQL Server database static data with ApexSQL Source Control

ApexSQL Source Control is a 3^rd party tool that supports all popular source control systems and can push database objects to a remote repository, compare the repository version of each object with the local one and pull the latest changes, while also tracking when and by whom the changes were made. ApexSQL Source Control also enables users to work with static data the same way they do with any other database objects. For the purposes of this article, we will use this tool and the AdventureWorks2014 database linked to a Git repository hosted on BitBucket.

1. First of all, the database has to be linked to source control and an initial commit has to be made. Detailed steps regarding this process can be found in the related article: Getting a database under version control: How to link a database to source control and initially commit objects. Keep in mind that this step is essential as static data is fundamentally bound to tables and constraints, so schemas must be committed to the repository beforehand, or together with static data.

2. In the Object Explorer, right click on a linked database and select More source control options -> Static data. Alternatively, when the appropriate database is selected, you can use the keyboard shortcut Ctrl+Alt+S.

   

3. This will open the Static data form, where a list of all database tables is displayed, unchecked by default. This form lets users select multiple tables and link them simultaneously.

   

4. In order to link static data, the user needs to check the desired tables and click the Link/Unlink button. In this example, we will choose the BusinessEntity, Currency and Department tables to be linked. Please consider that by clicking the Link/Unlink button all checked tables will be linked and all unchecked tables will be unlinked, consequently, if the user unchecks an already checked table, it will be automatically unlinked from static data.

   

5. The Action center will detect changes and after refreshing, static data will be displayed in the objects grid.

   

6. By checking all the tables, adding a comment and clicking the Apply button, static data will be committed to the repository and multiple users can start tracking them the same way they track changes of any other database object.

Instead of using the Static Data form, individual tables can be also linked to source control as static data through the Object explorer. To achieve that, right click on a table in Object explorer and select More source control options -> Link static data. The selected table will appear in the Action center and can be committed as described above. Please note that only tables with a valid primary key can be treated as static data.