Tag Archives: COMPRESS function in Sql Server 2016

GZIP COMPRESS and DECOMPRESS functions in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , , , ,

COMPRESS and DECOMPRESS are the new built in functions introduced in Sql Server 2016. This article explains these two new functions with extensive list of examples.

COMPRESS function compresses the input data using the GZIP algorithm and returns the binary data of type Varbinary(max).

DECOMPRESS function decompresses the compressed input binary data using the GZIP algorithm and returns the binary data of type Varbinary(max). We need to explicitly cast the output to the desired data type.

These functions are using the Standard GZIP algorithm, so a value compressed in the application layer can be decompressed in Sql Server and value compressed in Sql Server can be decompressed in the application layer.

Let us understand these functions with examples:

Example 1: Basic Compress and Decompress function examples

SELECT COMPRESS ('Basavaraj')

RESULT:
0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000

Let us decompress the above compressed value using the DECOMPRESS function by the following script

SELECT DECOMPRESS(
 0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000)

RESULT:
0x42617361766172616A

From the above result we can see that the result of the DECOMPRESS function is not the actual value but instead it is a binary data. We need to explicitly cast the result of the DECOMPRESS function to the datatype of the string which is compressed.

Let us cast the result of the DECOMPRESS function to Varchar type by the following statement.

SELECT CAST(0x42617361766172616A AS VARCHAR(MAX))

RESULT:
Basavaraj

Example 2: In this example COMPRESS and DECOMPRESS functions are used in one SELECT statement

SELECT 'Basavaraj' ValueToCompress,
  COMPRESS('Basavaraj') CompressedValue, 
  DECOMPRESS(COMPRESS('Basavaraj')) DeCompressedValue,
  CAST(DECOMPRESS(COMPRESS('Basavaraj')) AS VARCHAR(MAX))
     AS CastedDeCompressedValue

RESULT:
CompressAndDecompress

Example 3: In this example the value to be compressed is set to a variable

DECLARE @valueToCompress VARCHAR(MAX) = 'Basavaraj'
SELECT @valueToCompress ValueToCompress,
  COMPRESS(@valueToCompress) CompressedValue, 
  DECOMPRESS(COMPRESS(@valueToCompress)) DeCompressedValue,
  CAST(DECOMPRESS(COMPRESS(@valueToCompress)) AS VARCHAR(MAX))
   AS CastedDeCompressedValue

RESULT:
CompressAndDecompressVariable

Example 4: This example demonstrates the Compression of the same value of different type returns different encoded value

DECLARE @varcharValue VARCHAR(MAX) = 'Basavaraj', 
		@nVarcharValue NVARCHAR(MAX) = N'Basavaraj'
SELECT COMPRESS (@varcharValue) VarcharsCompressedValue 
SELECT COMPRESS (@nVarcharValue) NVarcharsCompressedValue

RESULT:
Different compressed result of the same value of different types

From the above result it is clear that both the variables have been set with same value but the compressed result is different because one variable is of type Varchar and other variable is of type NVarchar.

Example 5: This example demonstrates the importance of selecting the correct data type while casting the decompressed value

DECLARE @varcharValue VARCHAR(MAX) = 'Basavaraj', 
		@compressedValue VARBINARY(MAX)
--Compress the varchar value
SET @compressedValue = COMPRESS(@varcharValue) 
--Try to CAST the decompressed value as VARCHAR and NVARCHAR
SELECT @varcharValue varcharValue,  
 CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX)) 
         AS DecompressValueCastedToVarchar,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) 
         AS DecompressValueCastedToNVarchar

RESULT:
Casting decompressed value to different types

DECLARE @nVarCharValue NVARCHAR(MAX) = 'Basavaraj', 
		@compressedValue VARBINARY(MAX)
--Compress the nvarchar value
SET @compressedValue = COMPRESS(@nVarCharValue) 
--Try to CAST the decompressed value as VARCHAR and NVARCHAR
SELECT @nVarCharValue nVarcharValue,  
 CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX))
         AS DecompressValueCastedToVarchar,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX))
         AS DecompressValueCastedToNVarchar

RESULT:
Casting decompressed value to different types 2

From the above results it is clear that we need to CAST the decompressed value to the type of the actual original strings type to get the correct value

Example 6: This example demonstrates when the data compression will be effective

Let us execute the below statements

DECLARE @valueToCompress VARCHAR(MAX) = 'COMPRESS and DECOMPRESS'
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'
GO
DECLARE @valueToCompress VARCHAR(MAX) 
 = 'COMPRESS and DECOMPRESS are the new built in functions introduced in
    Sql Server 2016. This article explains these two new functions with 
	extensive list of examples.'
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'
GO
DECLARE @valueToCompress VARCHAR(MAX) 
 = 'COMPRESS and DECOMPRESS are the new built in functions introduced in Sql
    Server 2016. This article explains these two new functions with extensive
	list of examples. COMPRESS function compresses the input data using the 
	GZIP algorithm and returns the binary data of type Varbinary(max) ...  '
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'

RESULT:
Compression Effectiveness
From the above results it is clear that as length of the string to compress increases the compression effectiveness increases.

Example 7: This example demonstrates how we can use COMPRESS and DECOMPRESS functions while inserting and retrieving data to/from the table column

Let us create a Customer table by executing the following script. In this table the AdditionalInfo column holds the value compressed by the COMPRESS function

CREATE TABLE dbo.Customer
(Id INT IDENTITY(1,1), Name NVARCHAR(100),
  AdditionalInfo VARBINARY(MAX))

Let us insert a record in the customer table by using the following script. Here we are compressing the value for the AdditionalInfo column before storing in it

INSERT INTO dbo.Customer (NAME, AdditionalInfo)
VALUES('Basavaraj Biradar', 
	 COMPRESS(N'Holds master''s degree in computer
	  applications with gold medals from Gulbarga university'))

Let us now execute the following statement to get the inserted record from the Customer table. Here we are using the DECOMPRESS function to return the decompressed value of the AdditionalInfo column.

SELECT Id, Name, AdditionalInfo CompressedAdditionalInfo, 
  CAST( DECOMPRESS(AdditionalInfo) AS NVARCHAR(MAX))
				AS DecompressedAdditionalInfo 
FROM dbo.Customer

RESULT:
Compress and Decompress Table Example
[ALSO READ]