SQL 2016 DB engine features Compress and Decompress functions

SQL server added new function Compress and Decompress scalar valued function that take char/varchar as input and output a GZIP compressed varbinary data. This feature allows us to use column level compression of char/varchar data and reduces the cost of IO.

sql2016 compress
 

we can see we achieved almost 99% please note these results varies when data is stored in a table (row in flow)

Lets look at this in a detail example.

sql2016 compress2

 

sql2016 compress3

 

 

IO and time test

sql2016 compress4

 

Summary of comparision matrix between compress function and regular table we achieve 99% savings on storage on certain situations and achieve even more space when used in conjunction with row and page compression.

sql2016 compress5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s