Version-controlling Static Data: Conflicts

September 2nd, 2020

Version-controlling static data is vital to maintaining a normalized database structure. The article presents a simple and comprehensive guide through the process of resolving static data conflicts with the help of the dbForge Source Control plugin.

This is the second of the series of our articles on this subject, the first one was dedicated to setting up Source Control, linking static data to a version control repository, and making commits to it.

What is static data?

Static data, also referenced to as list, reference, or lookup data, is a set of predefined values that are never or rarely changed. In other words, static data represents non-transactional fixed data that doesn’t change often, for example, names of geographical locations, postal codes, city names, company departments, etc.

Below is the example of the syntax for creating a static data table:

 
CREATE TABLE [dbo].[City] (
        [CityID]     [int] IDENTITY(1, 1) NOT NULL,
        [Name]          [nvarchar](1) NULL,
        CONSTRAINT [PK__City__PRIMARY KEY (Cityid)]
        PRIMARY KEY
        CLUSTERED
        ([CityID])
    ON [PRIMARY]
) ON [PRIMARY]
GO

The role of static data in a database

Static data plays a key role in any normalized database structure. Typically static data is extensively referenced by transactional type data. Rather than use data repetitively in a table (for example, names of US cities), it is much more effective to create a table called cities, assign an ID to each city, and then reference the IDs in transactional data where they can be used hundreds of times. Thus, the cities table data will become static.

Why version-control static data?

As static data is vital to a database and an application built upon that database working properly, tracking changes to it is also important. dbForge Source Control allows you to quickly get all the necessary information about database static data: who, when, and how changed it. Version-controlling SQL Server database static data is also crucial for a successful CI/CD implementation.

How to version-control static data: conflicts

As long as two or more people work on the same database in parallel, conflicts are inevitable. A conflict occurs when two different users make simultaneous, different changes to the same table. In this case, a version control system can not determine whose changes are correct and marks those changes as having a conflict. The conflict must be resolved before performing Commit or Get latest operations.

dbForge Source Control helps effectively handle these conflicts by providing simple and user-friendly tools to resolve overlapping changes.

Let’s look closer at how to handle conflicts right in the Microsoft SQL Server Management Studio with the help of dbForge Source Control add-in.

NOTE:
Conflicts appear in the Dedicated database development mode only.

When dbForge Source Control detects a conflict, it marks the changes as conflict. You can view the overlapping changes in the comparison grid.

dbForge Source Control - conflicts

Suppose we decided to take data from the repository. In this case, the data in the SD_remote database with the same primary key values will be replaced with the data from the repository. To do this, in Conflicts, we need to select Get Remote.
If we want to overwrite the repository data with our changes, then we should select Get Local.
Let’s walk through both situations.

Getting Remote changes

To resolve a conflict by getting data from the remote project, select the checkbox with a corresponding change and click the Get Remote button. The Change Type will change from Conflict into Modify. Then click Get Latest.

Getting Remote changes with dbForge Source Control

As a result, data in the SD_remote database will be synchronized with the data from the repository in favor of the latest.

dbForge Source Control - projects synchronized

Getting Local changes

Suppose, we have another conflict between our branches, and in this case, we need our repository data to be overwritten with our local changes.

dbForge Source Control - conflict between branches

To do this, select the checkbox with a conflict and click the Get Local button. The change Type will change to Modify. Click Commit to make your changes appear in the repository.

dbForge Source Control -committing local changes

To check whether our actions were successful, let’s switch to our SD_local branch.

dbForge Source Control - check local changes

As you can see, our commit was successful and our changes appeared as remote in our second branch.

NOTE:
After you’ve clicked the Get Local button, you can still roll back your local changes. Select Undo in this case.

Conclusion

Every database development team needs a simple and efficient tool to manage changes and version-control code. dbForge Sorce Control is a perfect database change management solution. It seamlessly works with all popular version control systems and servers, including Azure DevOps, Apache Subversion (SVN), Team Foundation Server (TFS), TFVC, Git (including GitHub), Mercurial (Hg), Perforce (P4), SourceGear Vault. dbForge Sorce Control functionality is available as an SSMS add-in and dbForge Studio for SQL Server tool.

Leave a Comment