SQL Server: Compress and Decompress

You might be aware of the Gzip Compression algorithm. If not then try this link. 

So, SQL Server 2016 introduced these two awesome functions for compressing and decompressing your data.

Before SQL Server 2016, we had data compression features like page and row compression (I even wrote about those here), but those are different from this column value compression.

In the SQL Server 2016 compression function, data compression is done via the Gzip algorithm and returns VARBINARY(MAX).

Below is the simple syntax of Compress:

Compress (Expression)

Here, the expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n).

The Decompress function is just the opposite of Compress. It is used to decompress the value of VARBINARY, which is converted using the compression function. The only tweak you need to make is that you need to cast the output of Decompress in a specific data type to make it readable (if using varchar or nvarchar compression).

Decompress' syntax is:

Decompress (Compressed string)

Let’s understand this with an example.

In this example, I have taken three tables with the exact same schema and data.

1. IndiandotnetFriends.

2. IndiandotnetFriends_Compress.

3. IndiandotneFriends_Decompress.

You can see in the screenshot that we are inserting the same data. As the name suggests, in the first table, we have normal data from Adventureworks’ Person table. Now, in the second table, we are inserting the compressed value of FirstName, and in the third table, we are inserting decompressed value of FirstName from the compressed table.

Now, let’s check compress and decompress table data:

Now, you might be thinking that the output isn't readable. You're right. To make data readable from the Decompress table, we need to type cast.

See the picture below:

Now that we know how to use Compress and Decompress, let's go over the benefits. If you check out the picture below, you will, perhaps unsurprisingly, find that the compressed data is shorter than the normal and decompressed versions.

Obviously, compression helps you somewhere in the overall performance of your application. The main benefit is that you can pass the compressed data to your .NET application and decompress it using GzipStream as well. The only thing which we need to take care of is type casting. Suppose your compressed base column is VARCHAR — then you need to typecast again in VARCHAR.

Now, the next question is where we can use these functions. We can use them to compress large objects, like binary data in which we save JPGs, PDFs, Word docs, etc. 

In any case, I hope you're excited to use these functions in your own work.