Compression: should I compress the entire table or each index indivitually?

Question

Compression: should I compress the entire table or each index indivitually?

VoldemarG

Hall of Fame

Points: 3883
More actions
April 7, 2020 at 10:18 pm

#3741231

Should I not even bother compressing non-clustered indexes?
OR if I do, should I do one buy one non-clustered indexes? Or they won't get compressed?
what if my table does not have clustered indexes? it only has a composite primary key, non-clustered. And a dozen of nonclustered indexed. Should I then not consider such table for compression? Or it will still benefit from it?
Likes to play Chess

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

reka_liebe SSC Journeyman Points: 90 More actions · Answer 1

Before answering your question, first you need to priorities why you need compression. If the answer is you need to save space, then you will benefit from index compression. Beware that table compression increase cpu during read/writing data as additional step to compress data is needed.

this page have nice explanation about it.

https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

I thought that when I do the entire table compression with REBUILD ALL all (non clustered) indexes on the table get rebuilt. Or do I need to issue a separate statement for each index?

Likes to play Chess

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

as you were told on your other thread about this (and please do not create multiple threads) you do each individually - which by now you should already be aware of.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

reka_liebe wrote:

Before answering your question, first you need to priorities why you need compression. If the answer is you need to save space, then you will benefit from index compression. Beware that table compression increase cpu during read/writing data as additional step to compress data is needed.

this page have nice explanation about it.

https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/%5B/quote%5D

@reka_liebe...

There's no good way for you to know this so I'll tell you that this particular poster has many posts on this very subject over the last week or so. With that, I can he's already made this decision whether or not he's actually based it on your good suggestion. 😀

--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)