COMPRESS (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).

Topic link icon Transact-SQL Syntax Conventions

Syntax

COMPRESS ( expression )  

Arguments

expression
Is a nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) expression. For more information, see Expressions (Transact-SQL).

Return types

Returns the data type of varbinary(max) that represents the compressed content of input.

Remarks

Compressed data cannot be indexed.

The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.

Examples

A. Compress Data During the Table Insert

The following example shows how to compress data inserted into table:

INSERT INTO player (name, surname, info )  
VALUES (N'Ovidiu', N'Cracium',   
        COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));  

INSERT INTO player (name, surname, info )  
VALUES (N'Michael', N'Raheem', compress(@info));  

B. Archive compressed version of deleted rows

The following statement deletes old player records from the player table and stores the records in the inactivePlayer table in a compressed format to save space.

DELETE player  
WHERE datemodified < @startOfYear  
OUTPUT id, name, surname datemodifier, COMPRESS(info)   
INTO dbo.inactivePlayers ;  

See also

String Functions (Transact-SQL)
DECOMPRESS (Transact-SQL)