Why would I want to use Compress and Decompress functions?

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  April 4, 2020 at 2:44 pm

  #3740427

  in what live operational or development situation /requirement would I want to use the Compress function?

  like when should i become thankful for Compress and Decompress functions introduced in version 2016?

  Likes to play Chess

• frederico_fonseca

  SSCoach

  Points: 16237

  More actions

  April 4, 2020 at 3:10 pm

  #3740434

  for example when you have varchar max columns that compress very well (like email body (without attachments) and those columns will never be subject to a search.

  in one of my db's that is what I did to the email archive table - size went down from 1 TB to 300GB. (although I did use a inhouse CLR for compression as I implemented this in SQL 2008R2 - but had we been on a version that had compress I would have used it instead)

• Jeff Moden

  SSC Guru

  Points: 1004619

  More actions

  April 4, 2020 at 8:03 pm

  #3740480

  It's also great for the archival of data.  Please see "Example B" at the following link.

  https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver15

  It could also be an easy way to ZIP such data for data transmission files.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)