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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I understand how it works, but the book I'm studying doesn't tell me why you'd want to partition. Could someone shed some light? I can't understand any concept unless I see how it's useful.

share|improve this question

migrated from Jan 26 '12 at 21:17

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

up vote 10 down vote accepted

Basically, to split up a huge table into smaller sub tables or partitions

  • offload data into different files/filegroups (with changes in backup/restore strategy too)
  • separate "working" from "historic" data (without having 2+ separate tables which complicates queries)
  • allow compression of older data

We're talking 100s of millions of rows and/or high volumes.

You don't partition for a few million rows.

share|improve this answer
That, and the advantages of partition pruning (don't look in partitions if you know the data ain't there) and parallel execution (you can access multiple partitions at the same time to do a SUM or COUNT or other aggregate) – Konerak Jun 29 '10 at 9:07
Does it speed up SQL if you split it to multiple files? Isn't there a better archive strategy than just splitting it up? I'd have thought you would physically move the old data to a new table, or back it up / compress it to another computer somewhere, not just leave it in the same folder. – SLC Jun 29 '10 at 9:07
It'll also affect your indexes, so if you have a few years worth of archive data in one partition and only the present year in another, searching through the present year's partition involves only that partition's indexes. – David T. Macknet Jan 26 '12 at 14:56

Your Answer


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.