Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Undocumented uncompress() function behaves strangely

When evaluating the new feature compress() -> decompress() in Sql 2016 I accidentaly typed uncompress()
This function, or alias seem undocumented and compiles. But the results are invalid. It returns null values.
See the following example, where last row returns corrupt data.

declare @data varbinary(max) = (select compress(cast('text that will be lost forever' as varchar(max))))
select @data
select cast(decompress(@data) as varchar(max))
select cast(uncompress(@data) as varchar(max))

1 vote
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    The problem here is that UNCOMPRESS is not an alias for DECOMPRESS. It is an entirely different operation. UNCOMPRESS assumes that every byte of input is actually a Unicode code point in the range of U+0001 - U+00FF (i.e. values 1 - 255). This is the range that has 0x00 as the first byte, and so the "compression" was to simply remove the 0x00 bytes. The UNCOMPRESS function merely adds the 0x00 byte back in for each byte of the input value.

    For full details, please see my analysis of this function at:

    "What Does the Undocumented UNCOMPRESS Function Do?" ( https://sqlquantumleap.com/2019/02/26/what-does-the-undocumented-uncompress-function-do/ ).

    Also, please ignore at least one upvote on this item (currently the only vote for this item), as the ONLY reason I am voting for it is because this horrible UserVoice system does not provide any other mechanism for being notified of changes or responses to an item.

Feedback and Knowledge Base