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.
The syntax for creating a static data table can be as follows:
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]
GOThe 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 in the Microsoft SQL Server Management Studio with the help of the dbForge Source Control add-in.
Note
Conflicts appear in the Dedicated database development mode only.
When dbForge Source Control detects differences in two commits of the same file, it marks the changes as conflict. You can view the overlapping changes at the bottom panes: the left one displays the local changes, and the right one shows the remote changes.
Suppose we decided to get data from the remote 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.
If you want to update the local copy with the changes from the remote repository, use Get Remote.
If you want to overwrite the remote repository data with local changes, select the objects and click Get Local. Let’s walk through both situations.
Getting Remote changes
To resolve a conflict by getting data from the remote project, select the check box with the corresponding change and click Get Remote. The Change Type will change from Conflict to Modify. On the Changed tab, you can see the changes to be applied. Then, click Get Latest.
As a result, data in the SD_remote database will be synchronized with the data from the remote repository.
Getting Local changes
Suppose, we have another conflict between our branches. In this case, we want the repository data to be overwritten with the local changes.
To resolve a conflict by applying the local changes, select the check boxes with the conflict and click Get Local. The Change Type will change from Conflict to Modify. Then, click Commit to make these changes appear in the repository.
Note
If you want to roll back the local changes, click Undo before committing them to the repository.
To check whether the changes have been applied successfully, switch to the Source Control Manager of the SD_local branch and refresh it.
As you can see, the commit was successful and the changes are displayed as remote in another branch.
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 Server (previously known as Team Foundation Server (TFS)), Apache Subversion (SVN), TFVC, Git (including GitHub, GitLab, and BitBucket), Mercurial (Hg), Perforce (P4), SourceGear Vault.
dbForge Sorce Control functionality is available as an SSMS add-in and dbForge Studio for SQL Server tool.
