Partitioning

Beginning with the release of SQL Server 2005, SQL Server has supported table and index partitioning, which allows the data of partitioned tables and indexes to be spread across multiple filegroups in a database. The data is partitioned horizontally so that groups of rows are mapped into partitions using a partition function that defines the range of values in a partition. Partitions can also be stored on separate filegroups to further increase the scalability of a workload, improve performance, and ease maintenance operations:

Figure 11.1: A well-designed partition strategy can improve data loading and querying performance while also simplifying the maintenance of large volumes of data

Partitioning large tables or indexes can provide benefits that can improve manageability and increase performance, especially in the case of data warehouse workloads that often deal with large volumes of data.

Transferring or accessing subsets of data quickly...