locked
When do we use compress for table in Sql server RRS feed

  • Question

  • User283528319 posted

    hi all,

    today I saw <g class="gr_ gr_34 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="34" data-gr-id="34">sql</g> server has <g class="gr_ gr_67 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="67" data-gr-id="67">option</g> for compression tables

    when do we use it?

    Monday, March 11, 2019 8:36 AM

Answers

  • User-893317190 posted

    Hi fatihbarut,

    Compressing table could save the space of your hard disk and improve the performance of io when retrieving data from hard disk

    because sql server only needs to look for data in a smaller space.

    However , when communicating with  application (such as a webform application), it needs extra cpu cost to compress and decompress data.

    So you should consider whether to save space and improve io performance or  save cpu resource.

    For the full guide , please refer to https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 12, 2019 6:07 AM

All replies

  • User-893317190 posted

    Hi fatihbarut,

    Compressing table could save the space of your hard disk and improve the performance of io when retrieving data from hard disk

    because sql server only needs to look for data in a smaller space.

    However , when communicating with  application (such as a webform application), it needs extra cpu cost to compress and decompress data.

    So you should consider whether to save space and improve io performance or  save cpu resource.

    For the full guide , please refer to https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 12, 2019 6:07 AM
  • User283528319 posted

    <g class="gr_ gr_25 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="25" data-gr-id="25">Hi</g> <g class="gr_ gr_19 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="19" data-gr-id="19">fatihbarut</g>,

    <g class="gr_ gr_24 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="24" data-gr-id="24">Compressing</g> table could save the space of your hard disk and improve the performance of io when retrieving data from hard disk

    because <g class="gr_ gr_18 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="18" data-gr-id="18">sql</g> server only needs to look for data in a smaller space.

    <g class="gr_ gr_23 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="23" data-gr-id="23">However ,</g> when communicating <g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="22" data-gr-id="22">with  <g class="gr_ gr_21 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="21" data-gr-id="21">application</g></g> (such as a <g class="gr_ gr_15 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="15" data-gr-id="15">webform</g> application), it needs extra <g class="gr_ gr_16 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="16" data-gr-id="16">cpu</g> cost to compress and decompress data.

    So you should consider whether to save space and improve io performance <g class="gr_ gr_20 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="20" data-gr-id="20">or  save</g> <g class="gr_ gr_17 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="17" data-gr-id="17">cpu</g> resource.

    For the full <g class="gr_ gr_26 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="26" data-gr-id="26">guide ,</g> please refer to https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017

    Best regards,

    Ackerly Xu

    thanks. Another thing. Which compression should I <g class="gr_ gr_181 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="181" data-gr-id="181">chose</g>? row or index?

    Tuesday, March 12, 2019 6:12 AM
  • User-893317190 posted

    Hi fatihbarut,

    Row  compression is part of Page compression.You could refer to the link below to learn  the relationship between row compression and Page compression.

    https://thomaslarock.com/2018/01/when-to-use-row-or-page-compression-in-sql-server/

    As to index compression , when your table has  a clustered index,  the index compression is the same  as table compression.

    You could also compress non-clustered index.

    For more information , you could refer to https://dba.stackexchange.com/questions/49757/clustered-index-compression-vs-table-compression-are-they-the-same-thing

    Best regards,

    Ackerly Xu

    Tuesday, March 12, 2019 7:06 AM