Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. Is this a good idea? Or should I be storing indexes on a separate drive, like I was before? What are the pros or cons of either?
Putting an index on a partition scheme is exactly the same as putting a table on one. All it means in terms of storage is that the storage for the index is partitioned according to the partitioning column that you select. The partitions in a partition scheme still end up pointing to filegroups eventually, so you can still point the partition scheme at filegroups that are on your separate drives.
So the pros and cons are pretty much exactly the same as for table partitioning, and the query optimiser will make very similar choices in terms of partitioned .v. not. However, from my own experience, I can say that partitioning makes a lot of sense if you are regularly making queries that can be fully satisfied with the data from a single partition - and the sample applies to indexes.
