COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces two new system functions namely COMPRESS and DECOMPRESS.

This is Part 7 of 10 Part T-SQL Enhancements in SQL Server 2016 for Developers and DBAs. Click here to read it from the beginning….

COMPRESS Function: This function can be used to compress the input values using GZIP algorithm and the output will be of BYTE array of type VARBNARY(MAX).

DECOMPRESS Function: This function can be used to decompress the input value BYTE array using GZIP algorithm and it will return the BYTE array of type VARBINARY(MAX). To retrieve the actual value one would need to explicitly CAST result to a target type.

Example COMPRESS and DECOMPRESS function of SQL Server 2016

SELECT 
          'MyTechMantra' AS [Input Value To Compress]
          ,COMPRESS('SearchSQLServer') AS [Compressed Value]
          ,DECOMPRESS(COMPRESS('MyTechMantra')) AS [Decompressed Value]
          ,CAST(DECOMPRESS (COMPRESS ('MyTechMantra')) AS VARCHAR(MAX))AS [Derive Actual Input Value]
GO

Trending SQL Server Articles and Tips

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Topics

• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions