I would like to compress a Long Binary column in a table (email .msg files, PDF's, JPG's). We have a replicating DB's

If I apply ALTER Table ALTER Column COMPRESS at the consolidated DB (with passthrough) - will I have to dbunload -ar each remote DB and the consolidated to reclaim disk space ?

asked 03 May, 05:44

ThelmaCottage's gravatar image

accept rate: 0%

Yes and no.

If "reclaim disk space" means "reduce the size of the *.db file" then yes, recreating the *.db file from scratch (dbinit dbunload reload etc) is the only way to do that... it is not limited to COMPRESS.

If "reclaim disk space" means "reuse free space for new or expanding rows" then no, freshly-created free space in a table data page can be used for new data in the same table, and free pages can be used for anything, without having to recreate the *.db file.

FWIW you might also consider a deduplication algorithm if that is a common condition (same object stored in multiple rows).

permanent link

answered 03 May, 07:47

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

edited 03 May, 07:48

Just to add:

Whether your compressed data will really require less table pages as before (or even more) should be checked via the "sa_column_stats" system procedure - I had tested with "medium length" strings a while ago (256 bytes each) just to find out they were not compressed at all, which led to that DCX remark...

That being said, I guess for typical BLOBs like PDFs and images compression should acutally take place.

(03 May, 09:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 03 May, 05:44

question was seen: 71 times

last updated: 03 May, 09:20