Table partitioning in SQL 2008 - Why?

Question

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.

Answer 1

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.

• Konerak: 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)
• SLC: 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.
• David T. Macknet: 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.