| title | COMPRESS (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom |
|
||
| ms.date | 07/24/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | c2bfe9b8-57a4-48b4-b028-e1a3ed5ece88 | ||
| caps.latest.revision | 9 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]
Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).
Transact-SQL Syntax Conventions
COMPRESS ( expression ) 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).
Returns the data type of varbinary(max) that represents the compressed content of input.
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.
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)); 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 ;