• Not sure if a discussion about table partitioning versus partitioned views is relevant to the content of the article.  Having said that, I have recently been involved in a situation where both methods were employed in a very large operational data store used to service both online transactional application lookup, as well as daily analysis in several three dimensional cubes.

    The point is there is a place for both strategies.

    This is directly from a Microsoft whitepaper addressing partitioning strategies.

    In SQL Server 2005, queries against partitioned tables can use a new operator called demand parallelism. Demand parallelism is influenced by the system resources and MAXDOP settings.

    Queries that use partitioned tables will compile much faster than equivalent queries that use partitioned views. Query compilation time is proportional to the number of partitions when using partitioned views, while compilation is not affected by partition count when using partitioned tables.

    Queries against partitioned views might perform better in some cases. The following are examples of such cases:

    •The smallest unit of parallelism, when demand parallelism is chosen by the optimizer, is a partition. The performance of queries against a single partition in a SQL Server 2005 partitioned table might suffer because the degree of parallelism is limited to one. The same query against a partitioned view might perform better because of better parallelism within a partition.

     

    •Partitioned views might perform better when the number of partitions is less than the number of processors because of better use of the available processors through parallelism. When the number of partitions is greater than the number of processors, the performance of queries against partitioned tables can still suffer if the data is not evenly distributed across the partitions.

     

    •Queries against partitioned views might perform better when there is a skew in the distribution of data among the partitions.

    In other words, one method may or may not be the best way to achieve partitioning over another.  Each method has its benefits and drawbacks.  For example, with table partitioning you are physically limited to 1000 partitions.  Partitioned views have no such limitation.  In the case of a large data warehouse, its entirely possible to have more than 1000 physical partitions on a given dimension.  For example, a date dimension could very well be split into more than 1000 partitions...

    You can even have the situation where BOTH methods may be the course of action (partitioned view on a table partition).  Now I don't even know if this is possible, but for simplicity's sake, we won't make anyone's brain bleed right now.  The simple fact of the matter is, the best way is the way that fits your specific need.  And you don't get there without testing.  Testing itself can be a huge struggle as well, given the volume and specific type of data you may be dealing with.  The goal is, as always should be, to make the smallest possible change with the maximum beneficial return.