info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Server 2016 Compress() & Decompress()
by BF (Principal Consultant; Architecture; Engineering)
2017-01-01









COMPRESS (Transact-SQL)
- Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).

DECOMPRESS (Transact-SQL)
- Decompress input expression using GZIP algorithm. Result of the compression is byte array (VARBINARY(MAX) type).
- Cast result to a target type if needed.



Example:


Drop temp table is exists:

IF OBJECT_ID('Tempdb.dbo.#TMP', 'U') IS NOT NULL
Begin
Drop Table #TMP;
End


Create temp table:

CREATE TABLE #TMP (
WebSiteID INT IDENTITY (1,1) PRIMARY KEY,
WebSiteURL VARBINARY(MAX),
WebsiteShortDescription VARBINARY(MAX),
WebsiteLongDescription VARBINARY(MAX),
WebsiteFullDescription VARBINARY(MAX)
)
GO


Declare & populate variables:

Declare @varWebSiteURL VARCHAR(MAX) = 'www.TechDevOps.com'

Declare @varWebsiteShortDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'

Declare @varWebsiteLongDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'

Declare @varWebsiteFullDescription VARCHAR(MAX) = '
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication
A site for IT Operations, SysAdmins & DevOps featuring Microsoft + Cloud Computing + Data Replication'


Check DATALENGTH of variables:

SELECT
DATALENGTH(@varWebSiteURL) as 'DATALENGTH @varWebSiteURL',
DATALENGTH(@varWebsiteShortDescription) as 'DATALENGTH @varWebsiteShortDescription',
DATALENGTH(@varWebsiteLongDescription) as 'DATALENGTH @varWebsiteLongDescription',
DATALENGTH(@varWebsiteFullDescription) as 'DATALENGTH @varWebsiteFullDescription'


Insert into temp table while compressing the data:

INSERT INTO #TMP (WebSiteURL,WebsiteShortDescription,WebsiteLongDescription,WebsiteFullDescription)
Select COMPRESS(@varWebSiteURL), COMPRESS(@varWebsiteShortDescription), COMPRESS(@varWebsiteLongDescription), COMPRESS(@varWebsiteFullDescription)


Check DATALENGTH of compressed table data:

SELECT DATALENGTH(WebSiteURL) as 'DATALENGTH WebSiteURL(Compressed)', WebSiteURL AS 'Compressed Data', CAST(DECOMPRESS(WebSiteURL) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteShortDescription) as 'DATALENGTH WebsiteShortDescription(Compressed)', WebsiteShortDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteShortDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteLongDescription) as 'DATALENGTH WebsiteLongDescription(Compressed)', WebsiteLongDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteLongDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP
SELECT DATALENGTH(WebsiteFullDescription) as 'DATALENGTH WebsiteFullDescription(Compressed)', WebsiteFullDescription AS 'Compressed Data', CAST(DECOMPRESS(WebsiteFullDescription) AS VARCHAR(MAX)) 'Decompressed Data' FROM #TMP




As you can see in the diagram above, the longer the string the better compression works!!!



Note:

These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side.

DATALENGTH()
- Returns the number of bytes used to represent any expression.
- DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.