We know that SQL server Databases grow with time. Large SQL Server databases can become difficult to manage due to their size. Usually such large databases have only a couple of large tables that take up most of this size. By using partitioning you can make these tables easier to manage and can improve query performance by splitting the table into multiple storage units called filegroups based on the criteria specified by the user.
I will explain this in two parts:
· Part 1: Creating Partitions
· Part 2: Managing Partitions
Part 1: CREATING PARTITIONS
To partition a table or index you follow the following steps as show below in the flow chart.
1st step - CREATING PARTITION FUNCTION:
Partition Function is the first step. It defines the boundary values or points for partitioning data. The syntax for creating partition is:
Create Partition function PartitionFunctionName (date type)
As Range [LEFT/RIGHT]
For Values ( Boundary values...)
NOTE: Data types that you cannot use are text, ntext, image, xml, timestamp,varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.
Example1:
Create Partition function PFN_Example (int)
as RANGE LEFT for values (10,20,30,40,50)
RANGE LEFT in the above example shows that the boundary point resides in left hand partition.
| Partition | Values |
| 1 | -infinity to 10 |
| 2 | 11 to 20 |
| 3 | 21 to 30 |
| 4 | 31 to 40 |
| 5 | 41 to 50 |
| 6 | 51 to infinity |
If you had specified RANGE RIGHT then the table would look like below:
| Partition | Values |
| 1 | -infinity to 9 |
| 2 | 10 to 19 |
| 3 | 20 to 29 |
| 4 | 30 to 39 |
| 5 | 40 to 49 |
| 6 | 50 to infinity |
2nd Step: CREATING PARTITION SCHEME
Partition scheme is used to maps Partitions to filegroups. The syntax to create Partition Scheme is:
CREATE PARTITION SCHEME PartitionSchemeName
AS PARTITION PartitionFunctionName
TO (filegroupname..)
Example2:
CREATE PARTITION SCHEME PS_Example
AS PARTITION PFN_Example
TO ([FG],[FG2],[FG3],[FG4],[FG5],[FG6])
Go
So a Partition Scheme simply specifies a name for a physical storage structure. In the partition function example we created 6 partitions. So any values in Partition 1 resides in FG1, values in Partition 2 resides in FG2 and so on.
3rd Step: CREATING A PARTITION TABLE.
The syntax for creating a Partition Table is:
CREATE TABLE tablename
({<column_definition>}[ <table_constraint>])
[ON {PartitionSchemeName (PartitionColumnName) ¦Filegroup ¦”default}]
Example3:
CREATE TABLE Orders
( ordered int identity(1,1) primary key clustered,
Orderdetails varchar(30) Not Null,
City varchar(30) Null)
on PS_Example(orderid)
In the above example it stores the table in scheme PS_Example. It uses orderid to determine which rows fall in which partition. If we consider all three examples it will look like below:
Create Partition function PFN_Example (int)
as RANGE LEFT for values (10,20,30,40,50)
go
CREATE PARTITION SCHEME PS_Example
AS PARTITION PFN_Example
TO ([FG],[FG2],[FG3],[FG4],[FG5],[FG6])
Go
CREATE TABLE Orders
( ordered int identity(1,1) primary key clustered,
Orderdetails varchar(30) Not Null,
City varchar(30) Null)
on PS_Example(orderid)
When the data is added to the table and as the data grows, orderid <=10 will be in partition 1 and will be in FG1, orderid between 11 and 20 in partition 2 and FG2 and so on.
Step 4: CREATE PARTITIONED INDEX
Example 4:
CREATE NONCLUSTERED INDEX idx_ordered_city on Orders(City)
on PS_Example(orderid)
SQL Server will store the portion of the index that falls in the criteria orderid<=10 in FG1, store the portion from 11 to 20 in FG2 and so on.
PARTITIONING AN EXISTING TABLE or INDEX
1. Create a Partition Function
2. Create a Partition Scheme
3. Drop the existing Clustered index
4. Re-create the clustered index on the partition scheme.
Summary: In this section you’ve learnt how to create partitions. In part 2 I will cover how you can manage partition using SPLIT, SWITCH and MERGE.