Data Partitioning - Pro SQL Server Internals - page 298

Partitioned Tables
Table partitioning is an Enterprise Edition feature, which was introduced in SQL Server 2005. You can think of
partitioned tables as logical tables, which consist of multiple individual internal physical tables-partitions.
That terminology—logical and physical table—is not standard, although it describes it perfectly.

■ technically speaking, every table in SQL Server is partitioned. When a table is not partitioned by the user,
SQL Server treats it as a single-partition table internally.

Note

SQL Server tracks allocation units, such as IN-ROW, ROW-OVERFLOW, and LOB data, separately for each
partition. For example, a table with 10 partitions would have 30 different IAM chains per data file—one per allocation
unit per partition.
There are two additional database objects that are used together with table partitioning. A Partition Function
specifies boundary values, which are the criteria on how data needs to be partitioned. A Partition Scheme specifies
filegroups in which physical partition tables are stored.
Listing 15-1 shows the code that creates partitioned table OrdersPT with the data partitioned on a monthly
basis. This code assumes that the database has four different filegroups: FG2012 and FG2013 store data for years 2012
and 2013, respectively. FG2014 stores data for the first four months of 2014. Finally, the FASTSTORAGE filegroup stores
operational data starting from May 2014.

Listing 15-1. Creating a partitioned table

create partition function pfOrders(datetime2(0))
as range right for values
('2012-02-01', '2012-03-01','2012-04-01','2012-05-01','2012-06-01'
,'2012-07-01','2012-08-01','2012-09-01','2012-10-01','2012-11-01'
,'2012-12-01','2013-01-01','2013-02-01','2013-03-01','2013-04-01'
,'2013-05-01','2013-06-01','2013-07-01','2013-08-01','2013-09-01'
,'2013-10-01','2013-11-01','2013-12-01','2014-01-01','2014-02-01'
,'2014-03-01','2014-04-01','2014-05-01','2014-06-01','2014-07-01');

create partition scheme psOrders
as partition pfOrders
to (
FG2012 /* FileGroup to store data <'2012-02-01' */
,FG2012 /* FileGroup to store data >='2012-02-01' and <'2012-03-01' */
,FG2012,FG2012,FG2012,FG2012,FG2012
,FG2012,FG2012,FG2012,FG2012,FG2012
,FG2013 /* FileGroup to store data >='2013-01-01' and <'2013-02-01' */
,FG2013,FG2013,FG2013,FG2013,FG2013
,FG2013,FG2013,FG2013,FG2013,FG2013,FG2013
,FG2014 /* FileGroup to store data >='2014-01-01' and <'2014-02-01' */
,FG2014,FG2014,FG2014
,FASTSTORAGE /* FileGroup to store data >='2014-05-01' and <'2014-06-01' */
,FASTSTORAGE /* FileGroup to store data >='2014-06-01' and <'2014-07-01' */
,FASTSTORAGE /* FileGroup to store data >='2014-07-01' */
);