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.
+3
A:
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.
gbn
2010-06-29 09:04:22
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
2010-06-29 09:07:19
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
2010-06-29 09:07:40