YeshuaAgapao (1/28/2009)
Makes sense now. I would still suggest that if the running totals requirement was the dominant form of querying on the table, I would rather just un-partition the whole table rather than create a non-partitioned duplicate of it (temporarily with the temp table or worse, permanently with the non-paritioned indexed view). Otherwise I would really try not to fulfill any kind of realtime reporting requirement on it, only batch pre-generation of the aggregates.
I can't agree with un-partitioning the table. There may be other reasons that the table is partitioned. One reason, perhaps it isn't one million rows of data over ten years but 1 billion rows of data over ten years. With that, the majority of the queries against the table are limited to the current year and maybe the previous year with less than five percent of all queries access data older than that, but the data for all ten years needs to be available based on corporate policies.
Please remember, the actual purpose of this article wasn't just to show you how to compute running totals on partitioned tables. The purpose was to demonstrate a methodology that could possibly be used to address problems with updates to partitioned tables. Can it be used in all cases, maybe not, but it is a tool that is available.
Two other items. If you were to use the interim table method, you actually would only create a table with the necessary columns needed to complete the update. If the source table has 25 columns and is 2500 bytes wide, but you only need 6 columns and 75 bytes, that is all you would use. Same idea with the indexed view. You would only build the view using the columns you needed to perform the update.