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

  • VoldemarG

    Hall of Fame

    Points: 3640

    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?

    Voldemar likes to play CHESS (and IS good at it!)

  • reka_liebe

    SSC Journeyman

    Points: 89

    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: 3640

    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?

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14651

    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: 996502

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

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

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