Partitioning allows tables and indexes to be spread across multiple filegroups and consequently to different storage units

Partition Function

A partition function defines the criteria used to split the table data:

CREATE PARTITION FUNCTION mypfunc (int)
AS RANGE LEFT FOR VALUES (10, 20, 30, 40)

'RANGE VALUES' define the boundary points used to split the data. Keyword LEFT or RIGHT, determines which side of the boundary to place the boundary values. A partition function with n boundary points will define n+1 partitions.

Partition Scheme

A partition scheme is defined to map filegroups to a partition function:

CREATE PARTITION SCHEME mypscheme
AS PARTITION mypfunc TO
(FG1, FG2, FG3, FG4, FG5)

'AS PARTITION' clause specifies the partition function to use. 'TO' clause specifies the list of filegroups to match to the partitions defined by the function. The filegroups have to be defined in advance. Using 'ALL TO' instead of 'TO' allows you to assign all partitions to a single file group.

Partitioning Tables

Tables are assigned to partitions at creation using the 'ON' clause of the CREATE statement

CREATE TABLE (....) 
ON mypscheme(p_columnname)

The 'ON' clause specifies a partition scheme to use, and indentifies the column that will be used to split the table according to the partition function.

Partioning Indexes

Partition schemes can also be applied to indexes:

CREATE NONCLUSTERED INDEX idx_column_name 
ON table(column_name)
ON mypscheme(p_columnname)

A clustered index must be partitioned in the same way as the table because a clustered index must be stored in the same structure as the table. Non-clustered indexes do not need to be partitioned to match the table. A unique index or primary key on a partitioned table, must include the partition key as part of the index.

DROP DATABASE Robots
GO

CREATE DATABASE Robots ON 
PRIMARY
 (NAME = 'Robots', SIZE = 4MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%,
        FILENAME = 'C:\DATA\Robots.mdf'),
FILEGROUP FG1 DEFAULT
 (NAME = 'Robots_DAT1', SIZE = 2MB, FILENAME = 'C:\DATA\Robots_1.ndf'),
FILEGROUP FG2
 (NAME = 'Robots_DAT2', SIZE = 2MB, FILENAME = 'C:\DATA\Robots_2.ndf'),
FILEGROUP PART1
 (NAME = 'Robots_1_3',  SIZE = 2MB, FILENAME = 'C:\DATA\Robots_1_3.ndf'),
FILEGROUP PART2
 (NAME = 'Robots_2_3',  SIZE = 2MB, FILENAME = 'C:\DATA\Robots_2_3.ndf'),
FILEGROUP PART3
 (NAME = 'Robots_3_3',  SIZE = 2MB, FILENAME = 'C:\DATA\Robots_3_3.ndf')
LOG ON
 (NAME = 'Robots_LOG',  SIZE = 2MB, FILENAME = 'C:\DATA\Robots.ldf')
GO

USE Robots
GO

CREATE PARTITION FUNCTION datepartfunc (date)
AS RANGE RIGHT FOR VALUES('1/1/2008', '1/1/2009')
GO

CREATE PARTITION SCHEME datepartscheme
AS PARTITION datepartfunc TO (PART1, PART2, PART3)
GO

CREATE TABLE Robot(
	RobotID         INT             NOT NULL,
	OrderID         INT             NOT NULL,
	Model           VARCHAR(30)     NOT NULL,
	Cost	        MONEY           NOT NULL,
	ManufactureDate DATE            NOT NULL)
ON datepartscheme(ManufactureDate)
GO

CREATE CLUSTERED INDEX icx_manufacturedate
ON Robot(ManufactureDate)
ON datepartscheme(ManufactureDate)
GO

ALTER TABLE Robot
ADD CONSTRAINT pk_robot 
  PRIMARY KEY NONCLUSTERED (RobotID, ManufactureDate )
ON datepartscheme(ManufactureDate)
GO

Tables and indexes are said to be aligned when they are partitioned using the same partition function. If they both use the same partition function and the same partition scheme, then the storage is also aligned. Storage-aligning indexes and tables simplifies backup and restore operations as the indexes and tables are stored in the same storage structure

Managing Partitions

The 'SPLIT' and 'MERGE' operators can be used to manage partition function boundary points. SPLIT adds a new boundary to a partition function: MERGE removes a boundary point. Use ALTER PARTITION SCHEME to add a filegroup to an existing partition scheme