What is table partitioning in SQL Server?
Quick Answer
Partitioning splits one large table (and its indexes) horizontally into partitions based on a key range — typically a date. To queries it's still one table, but the engine can skip whole partitions (partition elimination), and you can load/archive data instantly by SWITCHing a partition in or out.
Partitioning chops a big table into pieces by a key, usually a date range, but it still looks like one table to queries. The engine can then do partition elimination — a query filtered to March only reads the March partition. The bigger benefit though is data management: you can rebuild indexes or archive data one partition at a time, and the sliding-window pattern lets you switch a whole month of old data out in a metadata-only operation instead of a giant DELETE. I'd be clear that it's mainly an administration and manageability feature, not a substitute for good indexes.
- Built from a partition function (the boundary values) and a partition scheme (which filegroup each range lives on).
- Main benefits: maintenance and data management — rebuild/reindex or switch out one month at a time; a sliding window pattern archives old data with a metadata-only
SWITCH. - Partition elimination speeds queries that filter on the partition key, but partitioning is not primarily a query-performance feature — good indexing usually matters more.
- Needs care: the partition key should be in the clustered index; queries not filtering on the key touch every partition.