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
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