A SQL Server feature that physically divides a large table into smaller, more manageable segments (partitions) based on a partition key, improving query performance, manageability, and maintenance for very large databases.
- Table partitioning: Assigns rows to partitions based on a partition function—commonly a date column for time-series data—enabling partition elimination to skip irrelevant data during queries.
- Partition switching: Allows near-instant archiving or loading of entire partitions by moving them between tables as a metadata operation, with no data movement required.
- Data lifecycle management: Partitioned tables are the foundation of efficient data lifecycle management in SQL Server, allowing new data to be loaded into staging, switched into production, and old data archived by switching partitions out.
- Index maintenance: Index rebuilds and reorganizations can be performed on individual partitions, dramatically reducing maintenance windows for very large tables.
-
Partitioned views: Provide similar benefits for non-Enterprise editions by distributing data across multiple tables using
UNION ALL. - Related terms: VLDB, Index Rebuild, Data Growth, Columnstore Index, Filegroup.
