SQL Server 2016 has introduced a couple of new functions that can be very useful and save storage space to boot. COMPRESS and DECOMPRESS offers the benefit of compressing column data thereby saving storage space. COMPRESS uses the GZIP compression algorithm and actually compresses data before it is stored in a column or variable and returns binary data. DECOMPRESS combined with CAST reverses the COMPRESS function returns the decompressed version of any compressed data.
I'll walk us through a simple example of using the new COMPRESS and DECOMPRESS functions using the AdventureWorks database. I first created both a compressed and uncompressed table to hold the exact same data so I can demonstrate the space savings using COMPRESS. Then, I loaded both tables with data from the person.person table in AdventureWorks.
CREATE TABLE PersonsCompressed (
FirstName nvarchar(50),
LastName nvarchar(50),
CompressedInfo varbinary(max))
GO
CREATE TABLE PersonsUNCompressed (
FirstName nvarchar(50),
LastName nvarchar(50),
UnCompressedInfo nvarchar(max))
GO
I made up a bunch of different length text values to put into the 3rd column in each table but here are my basic insert statements. I ran the insert statements many times until I had a significant amount of data in each table.
- Load table with compressed data using Person.Person table in AdventureWorks
INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)
SELECT FirstName, LastName, COMPRESS('What a nice person they are!....') FROM Person.Person
INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)
SELECT FirstName, LastName, COMPRESS('highly optimistic person who likes to laugh a lot.
Goofy. Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things.....') FROM Person.Person
GO
- Load table with UNcompressed data using Person.Person table in AdventureWorks
INSERT INTO PersonsUnCompressed (FirstName, LastName, UnCompressedInfo)
SELECT FirstName, LastName, 'What a nice person they are!....' FROM Person.Person
INSERT INTO PersonsUNCompressed (FirstName, LastName, UnCompressedInfo)
SELECT FirstName, LastName, 'highly optimistic person who likes to laugh a lot. Goofy.
Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things.....' FROM Person.Person
GO
After loading up my tables with a lot of records that are exactly the same except one has a compressed column this is what I see when I check how much space each table is taking: