7

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.

10

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.

3
  • 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
  • 1
    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
  • 2
    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 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.