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

    '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
	top 10000 replicate(cast('a' as varchar(max)),100000)
	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
	top 10000 compress(replicate(cast('a' as varchar(max)),100000))
	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

SQL Server 2016 – Drop If Exists

SQL Server 2016 has introduced many new features. One of them is Drop If Exists (DIE) , a DDL statement, which can be used to drop the objects if exists

In earlier version, you need query on the system functions or objects

Suppose you have a table named test and you want to drop it if it already exists in the database. In earlier versions, you can do

IF OBJECT_ID('test','U') is not null

Now from version 2016 onwards, you do not need an IF condition to find the existence of the table, use DROP IF EXISTS


It Drops the table if exists otherwise do nothing

You can also do the same thing for other objects as well like TRIGGER, PROCEDURE, etc

— To drop the Trigger named tr_test


— To drop the Trigger named proc_test


This is really a handy feature which is also available in MySQL

Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT

SQL Server 2016 has introduced some new T-SQL functions which are very handy to use. I will explore some of them starting with this post.

There are number of questions asked in the forums about splitting the values stored in the form of csv in the column. It has been always not a simple way to do this in SQL Server versions prior to 2016. But in 2016, with the introduction of STRING_SPLIT function this becomes easy. It accepts first parameter as the actual string to be split and second parameter as the delimiter.

Suppose you have a string “This,is,test” which you want to split and have them in separate rows. With STRING_SPLIT you can do it easily as shown below

declare @s varchar(100)
set @s='This,is,test'
select * from STRING_SPLIT(@s,',')

The result is


What if the values are in a column in table and you want split them?

You can use CROSS APPLY operator with this function

declare @products table
    product_id int,
    product_names varchar(1000)
insert into @products(product_id,product_names)
select 1,'Samsung,LG,Sony' union all
select 2,'Apple,Nokia,Micromax,Xiami'

    product_id,value as product_name
@products as t1
cross apply
select *
from STRING_SPLIT (t1.product_names,',')
) as t2

The result is

product_id  product_name
----------- -----------------
1           Samsung
1           LG
1           Sony
2           Apple
2           Nokia
2           Micromax
2           Xiami