Recently I was doing some work trying to figure out whether to use Table Partitioning or Partitioned views in a data warehouse to solve a problem. In this particular data warehouse the data cannot be partitioned by date, it’s actually partitioned by a value in a table. I was leaning towards Table Partitioning and talking with Brent (b | t) we put together this quick comparison up on a whiteboard:
|Table Partitioning||Partitioned Views|
|Query Partition Elimination||Y||Y|
|Index Tuning||EASY (single table means single set of indexes)||POWERFUL (different indexes on different tables)|
|Schema changes doable||N (single table means adding a column is time consuming)||Y (different tables can be different schemas and then managed through views)|
|Enforced Relational Integrity||Y||N|
|Freeze Data 10% R/O, Perfect Stat/Indexes||Y||Y|
As I said I was leaning towards table partitioning while Brent was leaning towards partitioned views. My argument was for referential integrity while Brent’s was ability of schema changes. Well turns out that Brent won (big surprise huh?) however not for any of the reasons listed. The reason we went Partitioned Views is that once that is in place we can then implement table partitioning on the individual tables. Combining these two techniques can be very powerful (if done correctly that is). If we went straight to table partitioning, we would be limited in our future potential in this regards.
So lesson of the day is when looking and planning architecture – think, whiteboard and then think some more – do not just open up SSMS and start typing code.