DECOMPRESS (Transact-SQL)

Article
11/30/2021
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

Note

This example does not apply to Azure Synapse Analytics.

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))  
);