How to Compress and Decompress an input expression in SQL Server

   

     Starting with SQL Server 2016, you can compress and decompress data in table rows. You can compress the data before you insert into a table or you can decompress the compressed data in a row. This is a great option when you need to archive your data in your tables. Both functions are using GZIP algorithm to compress or decompress the data. Let's start with the COMPRESS function.

COMPRESS(expression)

expression can be one of the followings:

nvarchar     varchar     char     nchar     binary     varbinary

      Function returns varbinary(max) that represents the compressed data. You can not index the compressed data. This function compress only the given expression, you may need to look at Row Compression implementation or Page Compression implementation methods if you need to compress the whole table or database. Let's look at an example. I am going to create a json variable, compress it and insert into a table.

Now If I query the table, here is how the data looks like. By the way my compressed column's data type is varbinary(max)

To Decompress a compressed expression, we need to DECOMPRESS function.

DECOMPRESS(expression)

      Function returns varbinary(max) which represents the decompressed data,we need to cast it another type after decompression. In the next example, I am going to decompress the value I have  compressed in the earlier example.