PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Compress and Decompress in SQL Server 2016

SQL Server 2016 ctp 3.2 is already available with a lot of news.

One of the new features available is the ability to compress and decompress fields during DML (insert/select/update) using the functions Compress and Decompress.

One important point to notice is that compressed fields cannot be indexed. On the other hand, if you will not search records by this field, may be the compression can be a good option.

Let’s see an example of how these functions work and the result of the compression.

compress1.png

We have 1012 Text_Mix_Page, besides the other types of pages.

The field with compressed information needs to be varbinary.

compress2.png

Now we have only 24 TEXT_MIX_PAGEs. Of course this ratio depends on the data in the table. If you have data already compressed, like jpg files, for example, the ratio won’t be so high and you will only spend cpu in the compress/decompress process.

We can decompress the information when we query the table. This is a sample query:

compress3.png

As you can see in the image above, the result of the decompress function is of type varbinary. We need to cast the result to see the original information. Let’s add the cast to the query:

compress4.png

The compression uses GZIP algorithm, so we can decompress the information in the client application, instead the query. The varbinary field is mapped to byte[] by entity framework. We can use GZipStream to decompress the information in .NET.

This is a sample code to decompress the field and display the result in a windows forms textbox: