GZip CLR Functions

Posted by Joe Enos on August 11, 2021 · 7 mins read

Looks like SQL Server 2016 finally introduced a COMPRESS function. If you’re not quite there yet, then you can build a CLR function to compress/decompress data using GZip.

using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[SqlFunction]
public static SqlBytes GZipString(SqlString value)
{
using (var inputStream = new MemoryStream(Encoding.UTF8.GetBytes(value.Value)))
{
using (var outputStream = new MemoryStream())
{
Compress(inputStream, outputStream);
return new SqlBytes(outputStream.ToArray());
}
}
}

[SqlFunction]
public static SqlBytes GZipBinary(SqlBytes value)
{
using (var inputStream = new MemoryStream(value.Value))
{
using (var outputStream = new MemoryStream())
{
Compress(inputStream, outputStream);
return new SqlBytes(outputStream.ToArray());
}
}
}

[SqlFunction]
public static SqlString GUnZipToString(SqlBytes value)
{
using (var inputStream = new MemoryStream(value.Value))
{
using (var outputStream = new MemoryStream())
{
Decompress(inputStream, outputStream);
return new SqlString(Encoding.UTF8.GetString(outputStream.ToArray()));
}
}
}

[SqlFunction]
public static SqlBytes GUnZipToBinary(SqlBytes value)
{
using (var inputStream = new MemoryStream(value.Value))
{
using (var outputStream = new MemoryStream())
{
Decompress(inputStream, outputStream);
return new SqlBytes(outputStream.ToArray());
}
}
}

private static void Compress(Stream inputStream, Stream outputStream)
{
using (var gzip = new GZipStream(outputStream, CompressionMode.Compress))
{
byte[] buffer = new byte[8192];
int count;
while ((count = inputStream.Read(buffer, 0, buffer.Length)) > 0)
{
gzip.Write(buffer, 0, count);
}
}
}

private static void Decompress(Stream inputStream, Stream outputStream)
{
using (var gzip = new GZipStream(inputStream, CompressionMode.Decompress))
{
byte[] buffer = new byte[8192];
int count;
while ((count = gzip.Read(buffer, 0, buffer.Length)) > 0)
{
outputStream.Write(buffer, 0, count);
}
}
}
}