Partitions and Indexes, in which cases should be used

Question

I know some knowledges:

• Partitions used to achieve better performance (like described in this article)

• And indexes used to increase performance in selection operations.

And as result my question: in which cases i should use indexes and in which cases i should use partitioned tables.

In other words what should be better in different operations(selections,deletions,updates) indexes or partitioning of table, and why.

Thanks.

Answer 1

Easy: you should always use indexes.

Partitioning for performance is probably the most misunderstood myth out there. When you partition, the best you can hope for is on-par performance with a non-partitioned table. And yes, that is including partition elimination enhancements. Reducing table scans to partition scans because of missing indexes is simply not the answer. Replacing table scans with index seeks or index range scans it is a much better the answer.

Partitioning is a great feature for data maintenance and administration and for efficient ETL switch-in and switch-out operations. For a good discussion of pros and cons of partitioning, see How To Decide if You Should Use Table Partitioning.