DECOMPRESS (Transact-SQL)

Article
05/23/2023

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

This function decompresses an input expression value, using the Gzip algorithm. DECOMPRESS returns a byte array in the varbinary(max) data type.

Transact-SQL syntax conventions

Syntax

DECOMPRESS ( expression )

Arguments

expression

A varbinary(n), varbinary(max), or binary(n) value. For more information, see Expressions (Transact-SQL).

Return types

A value of data type varbinary(max). DECOMPRESS uses the Gzip algorithm to decompress the input argument. You should explicitly cast the 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))
);

Share via