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.

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.

share|improve this question

migrated from stackoverflow.com Oct 31 '12 at 14:04

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
+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

 
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.