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.



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.

  • +1 @Remus Partitioning should only be used after careful evaluation. See Kendra Little's blog on whether to partition: brentozar.com/archive/2012/03/… If you don't know what you're doing with partitioning, performance will go south quickly. – brian Oct 31 '12 at 14:11
  • +1 Neither is a replacement for the other. Two very different technologies for two very different outcomes. – Thomas Stringer Oct 31 '12 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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