Exploring SQL server 2016 T-SQL functions- COMPRESS

One of the T-SQL functions introduced in SQL Server 2016 is COMPRESS function. It compresses the string values using GZIP algorithm. The advantage of GZIP is that it can be used in SQL Server as well as in the application layers. So you can easily have compressed data in SQL Server and DECOMPRESS it in the application layer and vice versa.

This function accepts any string value and returns binary data of type VARBINARY(MAX)

A simple example is

select
    'a' as original_data,
    compress('a') as compressed_Data

The result is

original_data compressed_Data
------------- ---------------------------------------------------
a 0x1F8B08000000000004004B040043BEB7E801000000

What is the advantages of using this function? It reduces the space required for storing big string values.
Let us consider the following two tables. #test with original data and #test_compressed with compressed data

-- Original Data stored in varchar datatype
 create table #test(data varchar(max))
 insert into #test
 select
	top 10000 replicate(cast('a' as varchar(max)),100000)
 from
	sys.columns as c1 cross join sys.columns as c2

 

-- Compressed Data stored as varbinary
 create table #test_compress(data varbinary(max))
 insert into #test_compress
 select
	top 10000 compress(replicate(cast('a' as varchar(max)),100000))
 from
	sys.columns as c1 cross join sys.columns as c2

Now analyze the space occupied

 exec tempdb..sp_spaceused '#test'
 exec tempdb..sp_spaceused '#test_compress'

The result is

data
Look at the values for the column named Data. The original value takes 1 GB whereas compressed value takes only 1.4 MB

Note : If you want to have historical data with lot of string columns and rarely queried, it is better to compress the values to save the space.

Other posts on new features of SQL Server 2016
Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT
SQL Server 2016 – Drop If Exists

Advertisements

4 thoughts on “Exploring SQL server 2016 T-SQL functions- COMPRESS

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