SQL Server Compress and Decompress Functions


By:   |   Updated: 2016-02-11   |   Comments (8)   |   Related: More > Compression

Problem

Data compression has been available natively since SQL Server 2014 with row level and column level compression, but this was done by enabling compression at either the page level or row level. In SQL Server 2016 CTP 3.1, we have newly introduced functions for compression by which specific data itself can be compressed. In this tip we are going to explore and see how this compression can be beneficial.

Solution

SQL Server 2016 CTP provides new built in functions for compression and decompression of data.

  • Compress: This function compresses the input data using the GZIP algorithm and returns the binary data of type Varbinary(max)
  • Decompress: This function decompresses the compressed input binary data using the GZIP algorithm and returns the binary data of type Varbinary(max).

The basic syntax for these functions are shown below, where the expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n).

COMPRESS ( expression )

DECOMPRESS ( expression ) 

COMPRESS and DECOMPRESS functions can be used as shown below:

SELECT COMPRESS ('Compress and Decompress Function SQL Server')

sql server compress

If we decompress the above output we get the following:

SELECT DECOMPRESS (0x1F8B080000000000040073CECF2D284A2D2E5648CC4B5170494D8671DD4AF3924B32F3F31482037D1482538BCA528B004E16B8732B000000)

sql server decompress

As we can see from the above output DECOMPRESS function does not return the string which we have compressed above, we need to explicitly CAST the result of the DECOMPRESS function to a string datatype.

SELECT CAST(0x436F6D707265737320616E64204465636F6D70726573732046756E6374696F6E2053514C20536572766572 AS VARCHAR(MAX))

sql server cast and decompress

COMPRESS and DECOMPRESS Examples

Now to explore this further let's create a standard table and insert some data using the COMPRESS function.

CREATE TABLE dbo.product
( Id INT IDENTITY(1,1),
  Name NVARCHAR(max),
  Description VARBINARY(MAX)
 )
GO
   
INSERT INTO dbo.product (Name, Description)
VALUES('TestDemo', 
     COMPRESS(N'This Demo is to show how we can use the new Compress and decompress function in sql server 2016 CTP 3.1 onwards'))

Now if we do a normal SELECT statement the output will be:

sql server compressed data example

So we have to use CAST as mentioned above to see the actual text. Here we are using CAST and DECOMPRESS to output the data to DecompressedTest.

SELECT Id, Name, description, 
  CAST( DECOMPRESS(description) AS NVARCHAR(MAX))
                AS DecomressedTest 
FROM dbo.product


using cast and decompress with sql server

Storage Savings for Compressed Data

Now let's see where compression can be useful. For this we created three strings having different data lengths and compared the lengths before and after compression.

DECLARE @TextToCompress1 VARCHAR(MAX)

DECLARE @TextToCompress2 VARCHAR(MAX)

DECLARE @TextToCompress3 VARCHAR(MAX)

SELECT @TextToCompress1 = 'Data Compression SQL SERVER'

SELECT @TextToCompress2 = 'The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial.'

SELECT @TextToCompress3='The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. This is demo purposes only.'

SELECT  
   DATALENGTH(@TextToCompress1) 'Before_compression-1',
   DATALENGTH(COMPRESS(@TextToCompress1)) 'After_compression-1',
   DATALENGTH(@TextToCompress2) 'Before_compression-2',
   DATALENGTH(COMPRESS(@TextToCompress2)) 'After_compression-2',
   DATALENGTH(@TextToCompress3) 'Before_compression-3',
   DATALENGTH(COMPRESS(@TextToCompress3)) 'After_compression-3'
   

compress and decompress comparison for sql server

We can see here compression is not useful for the small data lengths, but it could be very beneficial for large data lengths, so we need to evaluate as it may create more overhead if not planned properly.

Using the correct CAST options with DECOMPRESS

As we have seen, DECOMPRESS doesn't return the actual string unless we use CAST, so if the correct data type is not used while using DECOMPRESS it will not give the desired output.

DECLARE @varcharValue NVARCHAR(MAX) = 'SQL Server 2016 Compress and Decompress function', 
        @compressedValue VARBINARY(MAX)

SET @compressedValue = COMPRESS(@varcharValue) 

SELECT @varcharValue OriginalValue,  
 CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX))  AS Decompress1,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(10))  AS Decompress2,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(20))  AS Decompress3,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) AS Decompress4


using proper cast with decompress

Storage Savings and Performance Gains with Compression

Now let's look at another where compression is useful. We will create some data and store the same data compressed and uncompressed and compare the storage needs.

CREATE TABLE Test_uncompress (test varchar(max)) --table to hold uncompressed data

CREATE TABLE Test_compressed (test Varbinary(max)) -- table to hold compressed data

-- Now insert test data into uncompressed table
INSERT INTO Test_uncompress values (replicate('DemoCompress and uncompress function',5000))  
GO 200000

--Insert data into compressed table by doing compress of values stored in uncompressed table
INSERT INTO Test_compressed 
SELECT compress(test) from Test_uncompress 

If we look at sp_spaceused for both tables we can see the comparison below:

compression storage savings

We can see the data size is 20808 KB in the compressed table and 1600200 KB for the uncompressed table.

Also, if we do straight SELECTs from both tables we can also see a difference in performance, but this example does not CAST the data which may increase the time of the second query.

set statistics time on
set statistics IO on

SELECT TOP 1000 * FROM dbo.Test_uncompress
GO

SELECT TOP 1000 * FROM Test_compressed
GO

compression performance gains

As shown above, we can see both the logical reads and elapsed time are reduced for the compressed data thus having a performance benefit.

Note: One important point to note is that compressed data cannot be indexed.

Next Steps


Last Updated: 2016-02-11


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, August 07, 2018 - 8:21:48 AM - rajendra Back To Top

Hi Shanmukha

 

I will check if there is any way to do in SQL Server 2014.

 

Thanks

Rajendra


Monday, August 06, 2018 - 9:08:30 PM - Shanmukha Back To Top

Hello Rajendra,

Thank you for your reply. Do you know of any alternative way to do this using sql server 2014? 

 Thanks,

Shanmukha


Monday, August 06, 2018 - 3:31:53 PM - rajendra Back To Top

 Hi Shanmukha

These functions are supported starting SQL 2016.

Thanks

Rajendra


Monday, August 06, 2018 - 1:20:16 PM - Shanmukha Back To Top

How can I achieve the same thing using SQL Server 2014?

How to achieve this using SSIS 2014? 


Monday, May 23, 2016 - 3:26:58 AM - mkhalilpak Back To Top

 Hi, i was preparing a presentation for MS SQL new feature for our development department and found your articles very helpful and well explained using examples. Saved my time, thanks a lot for the knowledge sharing

khalil- www.instzone.com 

 


Friday, February 12, 2016 - 11:50:03 AM - JeremyH Back To Top

I will certainly test this myself later, but have you tried run comparisons on the compressed data? This could be a huge advantage when comparing large text strings.


Friday, February 12, 2016 - 9:18:29 AM - rajendra Back To Top

 

Hi Gennadiy 

 

Data compression has been available natively since SQL Server 2014 with row level and column level compression, but this was done by enabling compression at either the page level or row level. 


Thursday, February 11, 2016 - 2:44:29 PM - Gennadiy Chornenkyy Back To Top

The Data compression as a feature available from SQL Server 2008.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools