Post reply

Table Partition in Sql server with compression

  • March 19, 2020 at 6:36 am

    #3735507

    Hi Team,

    We are testing this in one of the test server.

    We have one table with 1.5 TB. Where I partitioned table on one of the column(date field).

    we did partitioned date filed as non-clustered index. Now my table is partitioned as expected.

    but my table does not have any clustered index as of now.So I want to create primary key constraint on that partitioned table with compression option. will it automatically compress my partition column data or do i need to again rebuild my partitioned nonclusted index with compression option?

    Please advice?

     

  • March 19, 2020 at 6:10 pm

    #3735666

    Compression is separate from partitioning. You can create a PK and have that index be compressed. If I read this correctly, you're asking if the nonclustered index will be compressed automatically? I don't believe that's the case. I believe you need to enable compression on the NCI and rebuild it.

    I might do that first, as the PK creation will rebuild the NCI as pages move.

  • March 19, 2020 at 6:23 pm

    #3735669

    From the sounds of it, you're a bit new to partitioning and all of the benefits and seriously negative caveats it can throw you.

    Let's start at the beginning... Why do you think you want to partition this table?  What advantage(s) do you think it will provide?

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

  • March 25, 2020 at 7:57 am

    #3736821

    Thanks,got it

  • March 25, 2020 at 12:32 pm

    #3736884

    dastagiri16 wrote:

    Thanks,got it

    If you say so. 😉 Just trying to figure out what your goal is so that we can help someone new at it.

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

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

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