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.

1 Answer 1

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, 2010 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, 2010 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, 2012 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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