The DECOMPRESS function decompresses the compressed input data in binary format (variable, column, or expression) using the GZIP algorithm.

The return type of the function is VARBINARY(MAX). Yes, you read it right—the result of the decompression is still a VARBINARY data type and if you want to get the original data type, you need to cast the result explicitly.

Consider the following example, where the input string is first compressed and then decompressed with the same algorithm:

DECLARE @input AS NVARCHAR(100) = N'SQL Server 2017 Developer''s Guide'; 
SELECT DECOMPRESS(COMPRESS(@input));  

Since the function DECOMPRESS is logically complementary to the COMPRESS function, you would expect to get the original input string as the result. The result is, however, in the binary format:

input
---------------------------------------------------------------------
0x530051004C00200053006500720076006500720020003200300031003700200044006500760065006C006F0070006500720027007300200047007500690064006500

To get the input string back, you need to convert the resulting data type to the initial data type:

DECLARE @input AS NVARCHAR(100) = N'SQL Server 2017 Developer''s Guide'; 
SELECT CAST(DECOMPRESS(COMPRESS(@input)) AS NVARCHAR(100)) AS input; 

Now you will get the expected result:

input
----------------------------------
SQL Server 2017 Developer's Guide

Notice an interesting phenomenon if you miss the correct original type and cast to VARCHAR instead of NVARCHAR:

DECLARE @input AS NVARCHAR(100) = N'SQL Server 2017 Developer''s Guide'; 
SELECT CAST(DECOMPRESS(COMPRESS(@input)) AS VARCHAR(100)) AS input; 

When you use the Results to Text option to display query results, the following result is shown in SSMS:

input
------------------------------------------------------------------
S Q L   S e r v e r   2 0 1 7   D e v e l o p e r ' s   G u i d e 

However, when the Results to Grid option is your choice, the output looks different, as shown in the following screenshot:

Moreover, if you change the original type and cast to the Unicode data type, the result is very strange. When you swap the data types in the input string and the casted result (input—VARCHAR, result—NVARCHAR), the query will work as follows:

DECLARE @input AS VARCHAR(100) = 'SQL Server 2017 Developer''s Guide'; 
SELECT CAST(DECOMPRESS(COMPRESS(@input)) AS NVARCHAR(100)) AS input;

The output looks strange and the same in all display modes:

input
---------------------------------
             e

To comment on this bizarre behavior: keep in mind that it is always good to cast to the original data type and not to rely on the conversion internals.