The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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

ThelmaCottage
1415617
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
28.1k477636916
accept rate: 20%

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

By RSS:

Answers

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:

×48
×12
×10

question asked: 03 May, 05:44

question was seen: 189 times

last updated: 03 May, 09:20