GZip CLR Functions

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);
            }
        }
    }
}

GZip with Pako

GZip is a super-fast compression technique, usable anywhere. In javascript, you can use the Pako library to compress your data.

Browser

<!DOCTYPE html>
<html>
<head>
    <title>Test Zip</title>
    </head>
    <body>
        <textarea id="inputText" style="width: 90%; height: 300px;"></textarea>
        <textarea id="outputText" style="width: 90%; height: 300px;"></textarea>
        <br>
        <input type="button" value="Go" onclick="go();">
        <script src="https://cdn.jsdelivr.net/pako/1.0.5/pako.min.js"></script>
        <script>
            function go() {
                console.log(document.getElementById("inputText").value.length);
                var compressedBytes = pako.gzip(document.getElementById("inputText").value);

                console.log("compressedBytes: " + compressedBytes.length);

                try {
                   var decompressedText = pako.ungzip(compressedBytes, { to: "string" });
                   document.getElementById("outputText").value = decompressedText;
                } catch (ex) {
                    console.log(ex);
                }
            }
        </script>
    </body>
</html>

NodeJS

const pako = require("pako");

const inputText = `旧団撃健載婚。西住姿要臣市未検成録止記悪転団。稿代江型集対著側込円号乳前爆下集広。稿界版陽更工振火国張産指天支日自果圧見。判社安団自外面用売神農更春日策現管野。線咲破面経速町日止満行直。集禁未化捕併投購加様塗抗録規開各在造能訴。断否界駅小安計注必式面柴。会継内欺受方添条武風復不断撃楽産支。

Hello world, this is a test!

知系締描文民法芸機各共作請始紹論明禁。者注省警権宅急今変埼問何文力。陣済当名屋康慣生用前更測説官類無。早高医教護告野滑綱混着特購加遅代逸通口戦。増再賀期木多合作就勢城析車人出済。訪任人改重江賞需農神書関。市理闘演礼場事付現運違有指的。心秋雄式作意切加県米外限竹真法鮮。足顔漁性倍何買真購話注私紙中際。`;

console.log(inputText);

const compressedBytes = pako.gzip(inputText);

console.log(`compressedBytes: ${compressedBytes.length}`);

try {
   const decompressedText = pako.ungzip(compressedBytes, { to: "string" });
   console.log(decompressedText);
} catch (ex) {
    console.log(ex);
}

GZip in .NET

Sample Code

public class Gzip {
    public 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);
            }
        }
    }

    public 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);
            }
        }
    }
}

This is generic to any stream, where you define the input and output streams and pass the references in. For example, to GZip a file into a byte array in memory, define a FileStream to open the file, and a MemoryStream to store the result.