SQL Server CLR Function on Azure SQL Server Managed Instance

When you need to deploy an assembly (.NET DLL) as a SQL Server CLR function on Azure SQL Server Managed Instance, the process differs from an on-premises installation. Locally hosted SQL Server lets you reference the file directly:

CREATE ASSEMBLY HelloWorld
FROM '<system_drive>:\Program Files\Microsoft SQL Server\100\Samples\HelloWorld\CS\HelloWorld\bin\debug\HelloWorld.dll'
WITH PERMISSION_SET = SAFE;

Azure SQL Managed Instance rejects file-based statements:

Msg 40559, Level 16, State 1, Line 10
File based statement options are not supported in this version of SQL Server.

Instead, you convert the DLL to its hexadecimal representation and pass that in:

CREATE ASSEMBLY HelloWorld
    FROM 0x4D5A900000000000
WITH PERMISSION_SET = SAFE;

Converting the DLL to hexadecimal

Use LINQPad or Visual Studio Code to convert the DLL. This C# reads the binary and writes a hex string, split into lines SQL Server will accept:

List<string> SplitToLines(string stringToSplit, int maximumLineLength)
{
	var words = stringToSplit.Split(' ').Concat(new[] { "" });
	words = words.Skip(1)
				 .Aggregate(words.Take(1).ToList(), (a, w) =>
					{
						var last = a.Last();
						while (last.Length > maximumLineLength)
						{
							a[a.Count() - 1] = last.Substring(0, maximumLineLength) + @"\";
							last = last.Substring(maximumLineLength);
							a.Add(last);
						}
						var test = last + " " + w;
						if (test.Length > maximumLineLength)
						{
							a.Add(w);
						}
						else
						{
							a[a.Count() - 1] = test;
						}
						return a;
					});

	var linesList = words.ToList();
	linesList[0] = "0x" + linesList[0];

	return linesList;
}

static string GetHexString(string assemblyPath)
{
	if (!Path.IsPathRooted(assemblyPath))
		assemblyPath = Path.Combine(Environment.CurrentDirectory, assemblyPath);

	StringBuilder builder = new StringBuilder();

	using (FileStream stream = new FileStream(assemblyPath,
		  FileMode.Open, FileAccess.Read, FileShare.Read))
	{
		int currentByte = stream.ReadByte();
		while (currentByte > -1)
		{
			builder.Append(currentByte.ToString("X2", System.Globalization.CultureInfo.InvariantCulture));
			currentByte = stream.ReadByte();
		}
	}

	return builder.ToString();
}

void Main()
{
	// get the file from the same location as the linqpad script is stored.
	var scriptPath = Path.GetDirectoryName(Util.CurrentQueryPath);

	// enter your dll name
	var dllName = "Fastenshtein.dll";

	// enter your hex output
	var hexOutput = "Fastenshtein.hex";

	var hexString = GetHexString(@$"{scriptPath}\{dllName}");
	var lines = SplitToLines(hexString, 256);

	File.WriteAllLines(@$"{scriptPath}\{hexOutput}", lines);

	foreach (var s in lines)
		s.Dump();

	"Finished...".Dump();
}

Example: a fast Levenshtein function

The example uses the Fastenshtein library, a .NET implementation of the Levenshtein algorithm for fuzzy string matching that runs a great deal faster than anything written in native T-SQL.

DROP FUNCTION IF EXISTS dbo.ufn_fast_levenshtein
GO

DROP ASSEMBLY IF EXISTS FastenshteinAssembly
GO

PRINT N'Creating CLR assemblies';
GO
CREATE ASSEMBLY FastenshteinAssembly AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000\
-- ... the rest of the hex output from the script above, one 256-character line at a time ...
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.ufn_fast_levenshtein( @value1 NVARCHAR(MAX), @value2 NVARCHAR(MAX))
RETURNS INT
AS
EXTERNAL NAME FastenshteinAssembly.[Fastenshtein.Levenshtein].Distance;
GO

Testing the function

-- Example, simple comparison.
DECLARE @retVal AS INTEGER;
SELECT @retVal = XtrlUtils.ufn_fast_levenshtein('Test', 'test');
SELECT @retVal;
GO

-- Example, fuzzy match names between two tables
SELECT SP.SalesPerson,
       ADM.MemberName,
       ADM.UserPrinicpalName
FROM   dbo.SalesPerson                   AS SP
       LEFT OUTER JOIN dbo.AzureADMember AS ADM ON dbo.ufn_fast_levenshtein(ADM.MemberName, SP.SalesPerson) < 3;
GO