SQL Server 2016 - All new fn - Compress and Decompress

Hello Friends!

I’m back to you with couple of new functions available in SQL Server 2016 version called “Compress” & “Decompress”. So far most of us are aware about PAGE LEVEL & ROW LEVEL compressions in SQL Server. However I have a had come to a situation, probably once of twice to look for something where data for particular string can be compressed & insert directly to table. Probably that’s the one of strong reason Microsoft has come up with these two new function. Lol! Why would Microsoft gets into my brain to design & deliver an enterprise solution. 🙂

These two functions use GZip algorithm to compress data in application layer or client side. This compressed data can easily cascade to SQL Server to get stored, or can compress data in SELECT query and DECOMPRESS it in client side. COMPRESS Function returns result as byte array of varchar(max).

Let me jump to explain this quickly with an example. In this example I am going to create simple table with binary data that will contain compressed content.

CREATE DATABASE [SQLNewFeatures]
GO
USE [SQLNewFeatures]
GO
CREATE TABLE SQLSchool (
 SQLStudent_ID int primary key identity,
 SQLStudent_Name nvarchar(max),
 SQLStudent_SurName nvarchar(max),
 SQLStudent_Address varbinary(max)
)
GO
----- Inserting COMPRESSED Data to Table
INSERT INTO SQLSchool (SQLStudent_Name, SQLStudent_SurName, SQLStudent_Address)
	VALUES ('Avanish','Panchal',COMPRESS('House No....Building No....Location...Landmark....City...State...Country...PinCode'))
GO
---- Selecting COMPRESSED & DECOMPRESSED Data together.
SELECT SQLStudent_ID
,SQLStudent_Address AS 'Compressed_Address'
,CAST(DECOMPRESS(sqlstudent_Address) AS VARCHAR(MAX)) 'Decompressed_Address'
FROM SQLSchool
GO