title: SQL CLR Function to decompress GZip data
body: <p>I have two CLR functions that I use to compress/decompress NVARCHAR data.</p>
  
  <pre><code>[SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
  public static SqlBytes ufn_GZipCompress(SqlString input) {
      if (input.IsNull || input.Value.Length == 0)
          return SqlBytes.Null;
  
      using (MemoryStream msInput = new MemoryStream(input.GetUnicodeBytes())) {
          using (MemoryStream msOutput = new MemoryStream()) {
              using (GZipStream deflateStream = new GZipStream(msOutput, CompressionMode.Compress, true)) {
                  byte[] buffer = new byte[32768];
                  int read;
                  while ((read = msInput.Read(buffer, 0, buffer.Length)) &gt; 0)
                      msOutput.Write(buffer, 0, read);
              }
  
              return new SqlBytes(msOutput.ToArray());
          }
      }
  }
  
  [SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
  public static SqlString ufn_GZipDecompress(SqlBytes input) {
      if (input.IsNull || input.IsNull)
          return SqlString.Null;
  
      byte[] buf = new byte[32768];
  
      using (MemoryStream msOutput = new MemoryStream()) {
          using (GZipStream deflateStream = new GZipStream(input.Stream, CompressionMode.Decompress, true)) {
              int bytesRead;
              while ((bytesRead = deflateStream.Read(buf, 0, 32768)) &gt; 0)
                  msOutput.Write(buf, 0, bytesRead);
          }
  
          return new SqlString(Encoding.UTF8.GetString(msOutput.ToArray()));
      }
  }
  </code></pre>
  
  <p>The problem is when I try to decompress binary data, I do not get the the output that is expected, for example:</p>
  
  <pre><code>SELECT dbo.[ufn_GZipDecompress](dbo.[ufn_GZipCompress](N'Hello World'))
  </code></pre>
  
  <p>Returns</p>
  
  <pre><code>H
  </code></pre>