How a value can be Compress and Decompress in Sql Sever? COMPRESS and DECOMPRESS functions in Sql Server 2016

COMPRESS and DECOMPRESS functions in Sql Server 2016

Sql Server 2016 introduced COMPRESS and DECOMPRESS are the new built in functions.

COMPRESS and DECOMPRESS functions are using the Standard GZIP algorithm. Values can be compressed and decompressed in Sql server as well as in application. A Value also can be compressed in Sql Server can be decompressed in the application layer and vice versa.

COMPRESS function compresses the value and returns the binary data. Its return type is Varbinary (max).

DECOMPRESS function decompresses the compressed binary data and returns the binary data of type Varbinary (max).

Remark: - We need to explicitly cast the binary data of type Varbinary (max) (decompressed output) to the native data type.

Example 1: Simple use of Compress and Decompress function

-------- COMPRESS function Example------------

SELECT COMPRESS ('Alok Kumar Singh') AS 'OUTPUT'

OUTPUT: - x1F8B080000000000040073CCC9CF56F02ECD4D2C5208CECC4BCF0000C4681A7A10000000

-------- DECOMPRESS function Example------------

SELECT

DECOMPRESS(0x1F8B080000000000040073CCC9CF56F02ECD4D2C5208CECC4BCF0000C4681A7A10000000) AS 'OUTPUT'

OUTPUT: - 0x416C6F6B

-------- CASTING to DESIRE OUTPUT------------

SELECT CAST(0x416C6F6B204B756D61722053696E6768 as varchar(max))

OUTPUT: - Alok Kumar Singh

Here is the simple Compress and decompress function in SELECT Statement.

SELECT 'Code-view.com' AS ACTUAL_VALUE,

      COMPRESS ('Alok Kumar Singh') AS  COMPRESSED_VALUE,

         DECOMPRESS(COMPRESS ('Alok Kumar Singh'))  AS DECOMPRESSED_VALUE,

        CAST(DECOMPRESS(COMPRESS ('Alok Kumar Singh')) AS VARCHAR(MAX)) AS  CASTING_to_DESIRE_OUTPUT

OUTPUT:-