SQL database project (SSDT) merge conflicts

I sometimes find working with Visual Studio projects a challenge in multi-developer environments, because each project type seems to have its own vulnerability to Git merge conflicts. In a previous post I described an approach to handling issues when working with SSAS tabular projects – here I look at a similar problem when working with SQL Server Data Tools to author SQL Server database projects.

SSDT projects are prone to merge conflicts when multiple developers create new objects in a SQL project, because all a project's objects are listed in a single .sqlproj XML file. When new objects are created, entries for those objects are added to the .sqlproj file, at the end of the list. In this example, I've added MyNewStoredProc.sql to the database project:

What I don't know is that someone else has created and merged a feature branch containing another new object – while I've been working on mine – so I get a merge conflict:

Git is unable to determine which of the lines added to the file contains the “correct” update (both numbered 62 in the side-by-side comparison above), so reports a merge conflict. The answer, of course, is both – but Git can't work that out all by itself.

The shape of a possible solution here is similar to the approach for the case of SSAS tabular:

• Before you commit you changes to Git…
• sort object entries in the .sqlproj file into alphabetical order…
• so that new entries are less likely to appear in the same place.

You can make changes to the .sqlproj file automatically, just before it is committed to Git, using a pre-commit hook. More detail about creating Git hooks can be found in my post about avoiding merge conflicts in SSAS tabular. Here's the contents of a pre-commit hook script that sorts SQL project files:

#!C:/Program\ Files/Git/usr/bin/sh.exe
 
# sort SSDT projects alphabeticallly to avoid merge conflicts
powershell.exe -NoProfile -ExecutionPolicy Bypass -File ".\HookScripts\Sort-SsdtProject.ps1"

The pre-commit hook script above uses PowerShell to execute Sort-SsdtProject.ps1, located in my repository's HookScripts folder. The script looks like this:

$scriptFolder = $MyInvocation.MyCommand.Path | Split-Path
Set-Location $scriptFolder
Import-Module $scriptFolder\HookFunctions\HookFunctions.psm1 -Force
 
$f = Resolve-Path -Path "..\DataProjects\MyDbProject\MyDbProject.sqlproj"
ConvertTo-OrderedSqlProject -ProjectFile $f
Invoke-Utility git add $f  # re-stage file after reordering

On line 6, the script calls ConvertTo-OrderedSqlProject on the specified .sqlproj file. This modifies the file, potentially sorting it into a different order. These new changes haven't been staged for git (using git add) – line 7 uses Invoke-Utility to call git add again, making sure that the new modifications are included in the commit.

Both ConvertTo-OrderedSqlProject and Invoke-Utility are PowerShell functions defined in my HookFunctions.psm1 module, imported on line 3.

The contents of a SQL database project – the object definitions and other files it contains – are enumerated in <ItemGroup> elements in the .sqlproj XML file. Each child of an <ItemGroup> element represents a single file, and contains the path to the file in its Include attribute (as you can see in the screenshot at the top of this page). ConvertTo-OrderedSqlProject sorts children of <ItemGroup> elements into alphabetical order of their Include attribute values.

Neither PowerShell nor .NET provides functionality to sort XML elements out of the box, so ConvertTo-OrderedSqlProject sorts them using an implementation of bubble sort. Other sorting algorithms with better performance are available, but bubble sort is easy to understand and implement, and when its inputs are already sorted (as they will almost always be in the case of the pre-commit hook) its complexity is comparable to other algorithms¹⁾.

With a pre-commit hook like this, my updated .sqlproj file is reordered before being committed, so the change looks like this:

Notice that MyNewStoredProc.sql is now first in the list (alphabetic) rather than last (most recently added), so no longer conflicts with SomeOtherStoredProc.sql. The branch can be merged without further intervention.

Resolving merge conflicts can be tricky and time-consuming. Doing so when your tooling creates conflicts is doubly annoying! The approach I've described here uses a git pre-commit hook to sort SSDT database project files into alphabetical order.

This approach reduces the likelihood of experiencing a conflict when new objects are created, but it doesn't eliminate it. If two developers add new objects which are sorted into the same place in the .sqlproj file, you'll still need to resolve that manually. If two developers modify the same object, independently and at the same time, you have a process conflict – avoid these by talking to one another to resolve collaboration issues 😊.