If I had not been able to change the application (as is often the case), I would have replaced the table with a view of the same name, and performed the compression and decompression within the database. Don’t forget though that generally this work is best done outside the database. However, if it does need to happen in the database, this section applies.

One of the great new features of SQL Server 2016 is the inclusion of native COMPRESS() and DECOMPRESS() functions. You’ll find details of the COMPRESS statement here:

https://msdn.microsoft.com/en-us/library/mt622775.aspx and details of the DECOMPRESS statement here: https://msdn.microsoft.com/en-us/library/mt622776.aspx.  If the customer had been already running SQL Server 2016, this would then have been easy, but sadly they were on an older version.

For versions that support SQLCLR, it’s relatively straightforward to create functions to perform compression. Here is a rough example of the code required:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.IO.Compression;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction(Name = "CompressData",
                                            DataAccess = DataAccessKind.None,
                                            SystemDataAccess = SystemDataAccessKind.None,
                                            IsDeterministic = true,
                                            IsPrecise = true)]

    public static SqlBytes CompressData(SqlBytes DataToCompress)

    {

        SqlBytes returnValue = SqlBytes.Null;

 

        if (!DataToCompress.IsNull)

        {

            using (var compressedData = new MemoryStream())

            using (var gZipOutputStream = new GZipStream(compressedData,
                                                         CompressionMode.Compress,
                                                         true))

            {

                gZipOutputStream.Write(DataToCompress.Buffer, 0, DataToCompress.Buffer.Length);

                gZipOutputStream.Flush();

                gZipOutputStream.Close();

                returnValue = new SqlBytes(compressedData.ToArray());

            }

        }

 

        return returnValue;

    }

 

    [Microsoft.SqlServer.Server.SqlFunction(Name = "DecompressData",
                                            DataAccess = DataAccessKind.None,
                                            SystemDataAccess = SystemDataAccessKind.None,
                                            IsDeterministic = true,
                                            IsPrecise = true)]

    public static SqlBytes DecompressData(SqlBytes DataToDecompress)

    {

        SqlBytes returnValue = SqlBytes.Null;

 

        if (!DataToDecompress.IsNull)

        {

            var copyBuffer = new byte[4096];

 

            using (var decompressedData = new MemoryStream())

            using (var gZipInputStream = new GZipStream(DataToDecompress.Stream,
                                                        CompressionMode.Decompress,
                                                        true))

            {

                int bytesReadFromInputStream;

 

                while ((bytesReadFromInputStream
                        = gZipInputStream.Read(copyBuffer, 0, copyBuffer.Length)) > 0)

                {

                    decompressedData.Write(copyBuffer, 0, bytesReadFromInputStream);

                }

                returnValue = new SqlBytes(decompressedData.ToArray());

            }

        }

 

        return returnValue;

    }

}