Home Forums SQL Server 2012 SQL 2012 - General partitioning a large dimension? RE: partitioning a large dimension?

  • May 9, 2014 at 8:14 am

    #1712196

    GavD (5/9/2014)

    The SQL CAT team's recommendation is to [font="Arial Black"]avoid partitioning dimension tables[/font]: http://blogs.msdn.com/b/sqlcat/archive/2013/09/16/top-10-best-practices-for-building-a-large-scale-relational-data-warehous.aspx

    I have inherited a dimension table that has almost 3 billion rows and is 1TB and been asked to look at partitioning and putting maintenance in place, etc. (first step is to compress)

    I'm not a DW expert so was wondering what are the reasons to not partition dimensions? I can find articles that reference the above article but not much that explains why.

    Thanks in advance.

    Are you sure that you're not mixing things up a bit? Dimension tables should be relatively small when compared to Fact tables and, as a result, shouldn't need to be partitioned.

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