Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

enter image description here

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.