Post reply

how to partition a table on multiple columns

  • August 10, 2010 at 3:43 am

    #142344

    Hi,

    I have a table workcachedetail with 40 million rows which has 8 columns.We decided to partition the table.Most of the queries in our environment uses 4 columns in the where clause or joins.If I partition the table with 4 columns then I may get better performance.Can we partition the table on multiple columns(like partitioning on id,deptid,designation)?

    Thanks,

    Vamsy

  • August 10, 2010 at 4:28 am

    #1204607

    I don't think that we can partition based on columns.

    As far table partitioning is concerned , we can partition the data horizontally not vertically,

    What you are expecting is very much achievable through views and for better performance you might go with indexed view to retrieve the desired results.

  • August 10, 2010 at 4:30 am

    #1204610

    vamshikrishnaeee (8/10/2010)

    Hi,

    I have a table workcachedetail with 40 million rows which has 8 columns.We decided to partition the table.Most of the queries in our environment uses 4 columns in the where clause or joins.If I partition the table with 4 columns then I may get better performance.Can we partition the table on multiple columns(like partitioning on id,deptid,designation)?

    Thanks,

    Vamsy

    no

  • January 13, 2016 at 11:35 pm

    #1851817

    You can partition by creating a persisted computed columns.

    https://msdn.microsoft.com/en-us/library/ms186241.aspx

  • January 13, 2016 at 11:48 pm

    #1851818

    vamshikrishnaeee (8/10/2010)

    If I partition the table with 4 columns then I may get better performance.

    Nope.

    If you're looking for improved performance, don't waste your time fiddling with partitioning. Partitioning is for data management.

    https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2016 at 6:53 am

    #1851882

    Deepa V (1/13/2016)

    You can partition by creating a persisted computed columns.

    https://msdn.microsoft.com/en-us/library/ms186241.aspx%5B/quote%5D

    Please explain how that would help in this case.

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

  • January 14, 2016 at 7:00 am

    #1851884

    vamshikrishnaeee (8/10/2010)

    Hi,

    I have a table workcachedetail with 40 million rows which has 8 columns.We decided to partition the table.Most of the queries in our environment uses 4 columns in the where clause or joins.If I partition the table with 4 columns then I may get better performance.Can we partition the table on multiple columns(like partitioning on id,deptid,designation)?

    Thanks,

    Vamsy

    Such vertical partitioning is known as "proper and effective indexing" and needs to be accompanied by properly written, SARGable code that can actually do an INDEX SEEK followed by a proper range scan. As Gail points out, other forms of partitioning don't actually help with performance of anything except things like index maintenance and, depending on the nature of the data and how the table is partitioned, backups. In fact, partitioning usually slows things down a bit because each partition has it's own B-Tree to traverse.

    --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 7 posts - 1 through 6 (of 6 total)

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