SQL 2016 COMPRESS and DECOMPRESS Functions

–By Lori Brown @SQLSupahStah

SQL Server 2016 has introduced a couple of new functions that can be very useful and save storage space to boot. COMPRESS and DECOMPRESS offers the benefit of compressing column data thereby saving storage space. COMPRESS uses the GZIP compression algorithm and actually compresses data before it is stored in a column or variable and returns binary data. DECOMPRESS combined with CAST reverses the COMPRESS function returns the decompressed version of any compressed data.

I’ll walk us through a simple example of using the new COMPRESS and DECOMPRESS functions using the AdventureWorks database. I first created both a compressed and uncompressed table to hold the exact same data so I can demonstrate the space savings using COMPRESS. Then, I loaded both tables with data from the person.person table in AdventureWorks.

CREATE TABLE PersonsCompressed (

FirstName nvarchar(50),

LastName nvarchar(50),

CompressedInfo varbinary(max))

GO

CREATE TABLE PersonsUNCompressed (

FirstName nvarchar(50),

LastName nvarchar(50),

UnCompressedInfo nvarchar(max))

GO

I made up a bunch of different length text values to put into the 3^rd column in each table but here are my basic insert statements. I ran the insert statements many times until I had a significant amount of data in each table.

— Load table with compressed data using Person.Person table in AdventureWorks

INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)

SELECT FirstName, LastName, COMPRESS(‘What a nice person they are!….’) FROM Person.Person

INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)

SELECT FirstName, LastName, COMPRESS(‘highly optimistic person who likes to laugh a lot. Goofy. Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things…..’) FROM Person.Person

GO

— Load table with UNcompressed data using Person.Person table in AdventureWorks

INSERT INTO PersonsUnCompressed (FirstName, LastName, UnCompressedInfo)

SELECT FirstName, LastName, ‘What a nice person they are!….’ FROM Person.Person

INSERT INTO PersonsUNCompressed (FirstName, LastName, UnCompressedInfo)

SELECT FirstName, LastName, ‘highly optimistic person who likes to laugh a lot. Goofy.

Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things…..’ FROM Person.Person

GO

After loading up my tables with a lot of records that are exactly the same except one has a compressed column this is what I see when I check how much space each table is taking:

That is a space savings of over 60% which can be significant in larger tables!

If you query the tables you can see that the CompressedInfo column is not displayed as the text value that was put in since it was compressed and stored as binary data.

We have to DECOMPRESS the CompressedInfo column and CAST it to the correct data type to read it.

There are negatives to the new functions. While MSDN states that compressed columns cannot be indexed (https://msdn.microsoft.com/en-us/library/mt622775.aspx ), I was able to add the column as an included column in an index. And you will likely have to change existing code to use the new functions.

So, next I want to know how performance is affected when having to compress and decompress data in queries. I found that there is considerably more Duration and CPU used when using DECOMPRESS in a query. That seems logical to me since the CPU is where I would expect the work of decompressing data to happen.

Interestingly, when using COMPRESS and inserting records it was basically the same as inserting without compressing the data.

A good scenario to use compressed columns would be to use them for archived data with text columns or data in tables that are infrequently accessed.

Hopefully, my small examples will give you some valuable insight into what to expect if you want to use the new functions. Enjoy!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!