DECOMPRESS (Transact-SQL)

10/11/2018
2 minutes to read

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

This function will decompress an input expression value, using the GZIP algorithm. DECOMPRESS will return a byte array (VARBINARY(MAX) type).

Topic link icon Transact-SQL Syntax Conventions

Syntax

DECOMPRESS ( expression )

Arguments

expression
A varbinary(n), varbinary(max), or binary(n) value. See Expressions (Transact-SQL) for more information.

Return Types

A value of data type varbinary(max). DECOMPRESS will use the ZIP algorithm to decompress the input argument. The user should explicitly cast result to a target type if necessary.

Remarks

Examples

A. Decompress Data at Query Time

This example shows how to return compressed table data:

SELECT _id, name, surname, datemodified,  
             CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info  
FROM player;

B. Display Compressed Data Using Computed Column

This example shows how to create a table for decompressed data storage:

CREATE TABLE example_table (  
    _id INT PRIMARY KEY IDENTITY,  
    name NVARCHAR(max),  
    surname NVARCHAR(max),  
    info VARBINARY(max),  
    info_json as CAST(DECOMPRESS(info) as NVARCHAR(max))  
);