Infrastructure at your Service

Stéphane Haby

SQL Server 2016: new T-SQL commands COMPRESS and DECOMPRESS

In live from the JSS2015 (Journée SQL Server 2015) in Paris, we saw that the CTP3.1 has been available in the night of these 2 days.

One new feature available for this release in the T-SQL domain is COMPRESS and DECOMPRESS.

Through a GZIP algorithm, you can compress and decompress an argument for nvarchar, varchar, varbinary, char and binary data types.
I create a table to test these new feature. The source table is a workload of the ErrorLog with three columns:

  • LogDate (datetime)
  • Process (varchar(20))
  • Text(varchar(MAX))

Compress01
The goal is to copy all rows from ErrorLog Table and compress the Column “Text” with a data type varchar(max) in a second ErrorLog table: ErrorLog2
Compress02
In the execution plan, the compression has an implicit conversion in a “compute scalar” operation.
The result of a compression on a column is a varbinary
If I do a simple select on these tables, the compressed column appears with some unreadable characters.
Compress04
I try to read the line with the DECOMPRESS option.
Compress05
In this case, you have not an implicit conversion like in the insert.
You can see that an error message 8116 appears with an invalid argument of the decompression function.
To have a varbinary, I used the CAST function
Compress07
With the CAST, the Column Text cannot be read correctly and I need to use a second time a CAST to have a varchar.

SELECT TOP 5 [LogDate] ,[ProcessInfo], CAST(DECOMPRESS(CAST(Text AS VARBINARY(MAX))) AS VARCHAR(MAX)) FROM [ErrorLog2]

Compress08
2 CAST to have the information is very awful! :-?
In a second time, I create a new table like ErrorLog but with a column Text in Varbinary(max): ErroLog3
Compress03
You can notice that no implicit conversion is done during an insert.
As before, I try to read the line with the DECOMPRESS option.
Compress09
I have no error message in this case but the Column Text cannot be read.
I must reuse a CAST to have a readable column

SELECT TOP 5 [LogDate] ,[ProcessInfo], CAST (DECOMPRESS(Text) as varchar(max)) FROM [ErrorLog3]

Compress10
Et Voila! I finish this test with a little tips: instead of decompressing with varchar I have to use nvarchar.
I let you see the result.
Compress11
Msdn reference for COMPRESS
Msdn reference for DECOMPRESS

 

 

Leave a Reply


five × = 30

Stéphane Haby
Stéphane Haby

Delivery Manager