Home › SQL Server › Compress/Decompress Functions and Performance considerations

Compress/Decompress Functions and Performance considerations

By Samir Behara on October 30, 2017 • ( 0 )

In earlier versions of SQL Server, we had Page level and Row level compression. With SQL Server 2016, there are 2 new built in functions which can help you Compress and Decompress text on the fly.

With SQL Server 2016 Service Pack 1, a number of Enterprise only features were made available in the Standard and Express editions of SQL Server. Compression/Decompression is available across all editions of SQL Server with 2016 SP1.

Compress Decompress

Compress function can be used on individual columns and input expressions. It uses the GZIP algorithm behind the scene and compresses the result to a byte array of type varbinary(max).


	–Demonstration of COMPRESS and DECOMPRESS Features
	SELECT COMPRESS ('New Features of SQL Server 2016')

	SELECT CAST(DECOMPRESS(0x1F8B0800000000000400F34B2D57704B4D2C292D4A2D56C84F53080EF451084E2D2A4B2D52303230340300FD90F96A1F000000) AS VARCHAR(MAX))

view raw

Compress Decompress in SQL Server 2016

hosted with ❤ by GitHub

Compress DB

The input expression can be of any of the character and binary datatypes – CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, VARBINARY.

Using Compression can help improve the overall performance in 3 primary ways —

Decreased Input/Output operation between client side and server side can result in improved performance.
Space used by the SQL Server tables will be decreased.
Very efficient when you are working with large objects like binary data.

Performance Considerations –

Compressed Column must be VARBINARY(MAX) only.
Compressed Column can’t be indexed – but do you actually need to index this column? Most probably not.
Compression/Decompression might require additional CPU resources and processing time.
Decompress function returns the values as VARBINARY(MAX) and needs to be cast to VARCHAR so that the content is readable.