Home Forums SQL Server 2005 Administering how to partition a table on multiple columns RE: how to partition a table on multiple columns

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