DECOMPRESS (Transact-SQL)

Applies to: yesSQL Server 2016 (13.x) and later YesAzure SQL Database YesAzure SQL Managed Instance yesAzure 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 )  

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

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

See Also

String Functions (Transact-SQL)
COMPRESS (Transact-SQL)