Answer
Table partitioning horizontally splits a large table into smaller, manageable partitions based on a partition key (often date or range-based IDs). Logically it remains a single table, but physically data is separated.
SQL Server implementation:
- Partition function: Defines boundary points for key ranges.
- Partition scheme: Maps partitions to one or more filegroups.
- The table or index is created ON the partition scheme, distributing rows across partitions based on the key.
Benefits:
- Maintenance operations (index rebuilds, statistics, archiving) can be targeted at specific partitions.
- Supports sliding window load/archive patterns via partition SWITCH operations.
- Can improve query performance via partition elimination, especially for time-sliced workloads.
Partitioning is not automatically faster; it must align with query predicates and maintenance strategies to be effective.