Partitioned Table and Index Concepts

Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.

Partitioned tables and indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to one server, you might want to implement a partitioned table instead.

Deciding whether to implement partitioning depends primarily on how large your table is or how large it will become, how it is being used, and how well it is performing against user queries and maintenance operations.

Generally, a large table might be appropriate for partitioning if both of the following are true:

• The table contains, or is expected to contain, lots of data that are used in different ways.

• Queries or updates against the table are not performing as intended, or maintenance costs exceed predefined maintenance periods.

For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access.

To expand on this example, suppose you want to move one month of read-only data from this table to a data warehouse table for analysis. With partitioning, subsets of data can be separated quickly into staging areas for offline maintenance and then added as partitions to existing partitioned tables, assuming these tables are all in the same database instance. Operations such as these typically take seconds, instead of the minutes or hours they took in previous releases.

Finally, partitioning a table or index might improve query performance if the partitions are designed correctly, based on the types of queries you frequently run and on your hardware configuration. For more information, see Designing Partitions to Improve Query Performance.

To provide an example of how a partitioning solution can be applied in a real-world database, a partitioning scenario that you can implement is available in the AdventureWorks sample database. This scenario is explained in Partitioning in the AdventureWorks Sample Database.