SQL SERVER 2016 – New T-SQL Functions – COMPRESS – DECOMPRESS

With every release of SQL Server, Microsoft has been adding enhancements to the product. Earlier I have written below blogs talking about new features/enhancements. Now I will write about features for SQL Server 2016 which are COMPRESS – DECOMPRESS.

• SQL Server – 2016 – New Feature: Dynamic Data Masking
• SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause
• SQL SERVER 2016 – Comparing Execution Plans
• SQL SERVER – 2016 – Opening JSON with OPENJSON()

In this blog, I would explore new string functions COMPRESS and DECOMPRESS which are newly available in SQL Server 2016 (CTP 3.1 onwards)

If you try this in an earlier version, then you would be welcomed with an error message.

Msg 195, Level 15, State 10, Line <>
‘COMPRESS’ is not a recognized built-in function name.
Msg 195, Level 15, State 10, Line <>
‘DECOMPRESS’ is not a recognized built-in function name.

Here is the version where it would work. Any version more than 801 should work.

Microsoft SQL Server 2016 (CTP3.1) – 13.0.801.12 (X64)
Dec  1 2015 15:41:43
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

Now, let’s understand the usage of COMPRESS and DECOMPRESS functions. The compression techniques which were available with earlier version of SQL Server was page level and row level compression. With the new function, we can specify string which needs to be compressed and insert directly. We need to remember that COMPRESS function gives output as byte array of VARBINARY(MAX) type. The algorithm used by these function is GZip.  https://en.wikipedia.org/wiki/Gzip So, an application can compress the data using standard Gzip algorithm and send it to SQL Server. Or Select compress data and decompress in the application.

Here is a quick example.

SET NOCOUNT ON
GO
USE tempdb
GO
DROP TABLE IF EXISTS Team_SQLAuthority;
GO
CREATE TABLE Team_SQLAuthority (
id INT PRIMARY KEY IDENTITY
,name NVARCHAR(MAX)
,surname NVARCHAR(MAX)
,info VARBINARY(MAX)
)
GO
INSERT INTO Team_SQLAuthority (
name
,surname
,info
)
VALUES (
'Pinal'
,'Dave'
,COMPRESS('I love SQL Server')
)
SELECT id
,info AS 'COMPRESSED-ed'
,CAST(DECOMPRESS(info) AS VARCHAR(MAX)) 'DECOMPRESS-ed'
FROM Team_SQLAuthority
GO

In above example, we are inserting compressed data in SQL Server. We can also select normal data, compress it on the fly via select statement and later client or application can decompress it using standard Gzip algorithm. This would reduce network usage.

The amount of compression would be dependent on type of data. If we have XML or JSON data, it might be compressed more.

DECLARE @STR1 VARCHAR(MAX)
DECLARE @STR2 VARCHAR(MAX)
SELECT @STR1 = 'I LOVE SQL SERVER'
SELECT @STR2 = 'I LOVE SQL SERVER AND ALL OTHER DATABASE PRODUCTS. 
LET US ADD MORE DATA TO SHOW THAT MORE
LENTH OF THE STRING CAN SHOW BETTER COMPRESSION BECAUSE THERE 
IS A OVERHEAD DUE TO COMPRESION ITSELF'
SELECT  DATALENGTH(@STR1) 'Original-1',
DATALENGTH(COMPRESS(@STR1)) 'Compressed-1',
DATALENGTH(@STR2) 'Original-2',
DATALENGTH(COMPRESS(@STR2)) 'Compressed-2'

Here is the output.

As we can see, longer string gets better compressed.

Do you think this feature would help you?

Reference: Pinal Dave (https://blog.sqlauthority.com)