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

screen-shot-2016-10-26-at-7-56-37-pm

Happy Learning!

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook

 

 

   

About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published.