Partitioning Examples

You can partition tables by, for example, column (such as customer name), expression, priority ranges, and status.

Partition Based on Columns

This statement creates a table that is partitioned by the "CustomerName" column. All rows with the same CustomerName are guaranteed to be colocated in the same process space. Here, the SERVER GROUPS clause determines the peers and servers that host data for the partitioned table. A server group is a subset of all the peers and servers that host data in the distributed system.

CREATE TABLE Orders 
( 
OrderId INT NOT NULL, 
ItemId INT, 
NumItems INT, 
CustomerName VARCHAR(100), 
OrderDate DATE, 
Priority INT, 
Status CHAR(10), 
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId) 
) 
PARTITION BY COLUMN ( CustomerName ) 
SERVER GROUPS ( OrdersDBServers);

Partition Based on Ranges

When you use the PARTITION BY RANGE clause, specify a column with multiple ranges of values to use for partitioning. The following example specifies partitioning based on three ranges of values for the "Priority" column:
CREATE TABLE Orders 
( 
OrderId INT NOT NULL, 
ItemId INT, 
NumItems INT, 
CustomerName VARCHAR(100), 
OrderDate DATE, 
Priority INT, 
Status CHAR(10), 
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId) 
) 
PARTITION BY RANGE ( Priority ) 
( 
VALUES BETWEEN 1 AND 11, 
VALUES BETWEEN 11 AND 31, 
VALUES BETWEEN 31 AND 50 
);

Partition Based on a List

When you use the PARTITION BY LIST clause, specify a column name and one or more lists of column values to use for partitioning. The following example partitions the table based on three different lists of values for the "Status" column:
CREATE TABLE Orders 
( 
OrderId INT NOT NULL, 
ItemId INT, 
NumItems INT, 
CustomerName VARCHAR(100), 
OrderDate DATE, 
Priority INT, 
Status CHAR(10), 
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId) 
) 
PARTITION BY LIST ( Status ) 
( 
VALUES ( 'pending', 'returned' ), 
VALUES ( 'shipped', 'received' ), 
VALUES ( 'hold' ) 
);

Partition Based on an Expression

Expression partitioning partitions a table by evaluating a SQL expression that you supply. For example, the following statement partitions the table based on the month of the OrderDate column, using the MONTH function as the SQL expression:
CREATE TABLE Orders 
( 
OrderId INT NOT NULL, 
ItemId INT, 
NumItems INT, 
CustomerName VARCHAR(100), 
OrderDate DATE, 
Priority INT, 
Status CHAR(10), 
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId) 
) 
PARTITION BY ( MONTH( OrderDate ) );