SQLRx Tip of the Month
 
June 2016
______________________________________________________________________________
SQL 2008

SQL Server Administration: SQL 2016 COMPRESS and DECOMPRESS Functions

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:

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!
___________________________________________________________________________
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
Be sure to check out our blog for more SQL Server information.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email me at lorib@sqlrx.com.


 

Phone: 214.526.7680 x 113

Toll free:  800.85.SQLRx  (x113)

blog.sqlrx.com

@SQLRx

LinkedIn

Facebook
 


 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit


 

30 Years of Credibility & Trust


 

Join our email list          Check out our events

 


Integrated Services, Inc. | 4144 N. Central Expwy, Suite 430 | Dallas | TX | 75204