SQL 2016 COMPRESS and DECOMPRESS Functions

By Lori Brown | Beginner

Jun 16

–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 3rd 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:

Blog_20160616_1

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.

Blog_20160616_2

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

Blog_20160616_3

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.

Blog_20160616_4

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

Blog_20160616_5

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!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>