Answer by Magnus Ahlkvist ·
With a partitioned view, you can achieve more or less the same thing as you do with a partitioned table. You even get proper partition elimination when using partitioned views, just as with partitioned tables. What you need to achieve that is to have check constraints on each of the tables underneath the partitioned view, much the same as you would have boundaries in the partition function for a partitioned table.
If this is a good idea or not is a whole other thing. It depends on how the table is searched. If the partition key is always a part of the queries, ideally selecting single partitions/tables you will most probably benefit for all your queries. If you however have queries like SELECT MAX(somecolumn) WHERE <predicate which will have to look into several or even all partitions>, you'll get terrible performance instead. You can overcome the later scenario by rewriting the queries, but now comes the downside of partitioned views vs partitioned tables. With partitioned tables, you can easily cross apply against sys-views and that way get the max from within each partition and then get the max from that result set. With a partitioned view, it's not at all as easy to dynamically find all the partitions, simply because there aren't sys-views to support it.
So I wouldn't be awfully concerned about the number of indexes - you can easily automate the creating of new tables and indexes when you need to add a new partition to the partitioned view. What I would be concerned about is the ability to foresee all queries that you new partitioned view need to support.