Solomon Rutzky (5/4/2010)
...you make a good point about the non-partitioned index. However, it is not that applicable in my situation. The index comes with two costs: disk space (in this case over 1 GB!) and it needs to be maintained across INSERT and DELETE operations.
I guess it comes down to circumstances and personal preference.
Not looking to start a debate here, but I just want to respond briefly:
The non-partitioned index could replace the partitioned index, so no additional space or maintenance would be required. I'm not sure the space or maintenance overhead arguments are terribly strong ones.
Your precise circumstances may differ, but I think that in general, I would prefer the solution that just works every time, without having to rewrite queries, or remember to do the trick whenever new code is written containing MIN or MAX. I dislike the $PARTITION approach for similar reasons, but it does strike me as preferable to the WHILE loop + hard coded partition count.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi